SQL FOREIGN KEY

What is a Foreign Key?

A FOREIGN KEY is a column (or a set of columns) in one table that uniquely identifies a row of another table. It creates a relationship between two tables, ensuring referential integrity. The foreign key in the child table references the primary key in the parent table.

Syntax of FOREIGN KEY

The basic syntax for defining a foreign key is as follows:

CREATE TABLE child_table (
      column1 datatype,
      column2 datatype,
      FOREIGN KEY (foreign_key_column) REFERENCES parent_table(primary_key_column)
    );

Example of Using FOREIGN KEY

Here’s an example of creating two tables with a foreign key relationship:

CREATE TABLE Customers (
      CustomerID INT PRIMARY KEY,
      CustomerName VARCHAR(100)
    );
    
    CREATE TABLE Orders (
      OrderID INT PRIMARY KEY,
      OrderDate DATE,
      CustomerID INT,
      FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    );

This ensures that each order is associated with a valid customer.

Cascading Actions

Foreign keys can also enforce cascading actions, which automatically update or delete related records. For example:

CREATE TABLE Orders (
      OrderID INT PRIMARY KEY,
      OrderDate DATE,
      CustomerID INT,
      FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) 
      ON DELETE CASCADE ON UPDATE CASCADE
    );

This means if a customer is deleted, all their orders will also be deleted.

Important Considerations

When using the FOREIGN KEY constraint, keep in mind:

• A foreign key must reference a primary key or a unique key in another table.
• The data type of the foreign key column must match the data type of the referenced column.
• You can have multiple foreign keys in a table.
• Foreign keys help maintain data integrity by preventing invalid data entries.