SQL Foreign Key

Connecting tables together

Relational databases store related information in separate tables and connect them using foreign keys. A foreign key is a column in one table whose values must correspond to values in a primary key (or unique column) of another table. This enforces referential integrity: it becomes impossible to record a row that points to a parent that does not exist, such as an order for a customer who was never created.

The table that contains the foreign key is called the child, and the table it points to is called the parent. The database checks the relationship on every insert and update, and it also protects the parent from being deleted while children still depend on it.

Defining a foreign key at creation

CREATE TABLE Customers (
    CustomerID INT NOT NULL PRIMARY KEY,
    Name       VARCHAR(100) NOT NULL
);

CREATE TABLE Orders (
    OrderID    INT NOT NULL PRIMARY KEY,
    OrderDate  DATE NOT NULL,
    CustomerID INT,
    CONSTRAINT fk_customer FOREIGN KEY (CustomerID)
        REFERENCES Customers (CustomerID)
);

Any CustomerID placed in Orders must already exist in Customers. An attempt to insert an order with an unknown customer is rejected by the database.

Referential actions

You can tell the database what should happen to child rows when the parent row they reference is updated or deleted. These optional rules are written with ON DELETE and ON UPDATE clauses.

• CASCADE - automatically apply the same change to the child rows.
• SET NULL - set the foreign key column in the child rows to NULL.
• RESTRICT or NO ACTION - block the change while matching child rows still exist.
• SET DEFAULT - set the child column to its declared default value.

ALTER TABLE Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID)
ON DELETE CASCADE;