SQL Foreign Key

A FOREIGN KEY is a column (or collection of columns) in one table that links directly to a PRIMARY KEY or UNIQUE key in another table.

Foreign keys enforce referential integrity, ensuring that relationships between tables remain consistent. They prevent users from adding records to a child table if there is no corresponding parent record, and they block changes to parent records that would leave orphaned child records.

Parent Table vs. Child Table

To understand foreign keys, consider the relationship between two entities:

Parent Table (Referenced Table): Contains the primary key column being referenced (e.g., Customers).
Child Table (Referencing Table): Contains the foreign key column pointing to the parent table (e.g., Orders).

Notice how CustID in the Orders child table directly connects to CustomerID in the Customers parent table, guaranteeing that an order cannot exist without a valid customer.

Foreign Key Syntax & Examples

1. Defining a Foreign Key at Table Creation

You can declare a foreign key constraint inline or at the table level using the REFERENCES keyword:

-- Step 1: Create Parent Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

-- Step 2: Create Child Table with Foreign Key
CREATE TABLE Orders (
    OrderID    INT PRIMARY KEY,
    OrderDate  DATE NOT NULL,
    CustomerID INT,
    -- Foreign Key Constraint Declaration
    CONSTRAINT FK_Orders_Customers 
        FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

2. Adding a Foreign Key to an Existing Table

If both tables already exist, use ALTER TABLE to attach the constraint rule:

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Referential Integrity Actions (`ON DELETE` & `ON UPDATE`)

What should happen to child records in Orders when a corresponding customer record in Customers is updated or deleted?

SQL lets you specify custom referential actions using ON DELETE and ON UPDATE clauses:

Action	Behavior
`NO ACTION` / `RESTRICT`	(Default) Blocks the `DELETE` or `UPDATE` on the parent table if matching child rows exist. Raises an error.
`CASCADE`	Automatically deletes or updates matching rows in the child table when the parent row is deleted or updated.
`SET NULL`	Sets the foreign key column in the child table to `NULL` when the referenced parent row is deleted or updated.
`SET DEFAULT`	Sets the foreign key column in the child table to its defined default value when the parent row is modified.

Example: Cascading Deletes

If a customer account is deleted, automatically remove all associated orders:

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

Dropping a Foreign Key Constraint

To remove a foreign key constraint without deleting the column or table data:

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE Orders
DROP CONSTRAINT FK_Orders_Customers;

-- MySQL / MariaDB
ALTER TABLE Orders
DROP FOREIGN KEY FK_Orders_Customers;

Foreign Key Rules & Pitfalls

Data Type Matching: The foreign key column in the child table must match the exact data type (and size) of the referenced primary key column in the parent table.
NULL Values Permitted: Foreign key columns can store NULL values unless explicitly declared as NOT NULL. A NULL foreign key signifies an unassigned relationship.
Index Performance: Always create an index on foreign key columns in child tables. Database engines do not always index foreign key columns automatically, and indexing them accelerates JOIN query execution times.