SQL FOREIGN KEY

A foreign key says "this column points at a row in another table". The database makes sure the target actually exists.

Declaring one

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  total       DECIMAL(10,2),
  CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

What it prevents

• Inserting an order whose customer_id doesn't exist in customers.
• Deleting a customer who still has orders (unless you set ON DELETE behaviour).

ON DELETE / ON UPDATE

FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE
  ON UPDATE RESTRICT

Performance tip

The parent column being referenced must be indexed (the primary key already is). Most engines also recommend indexing the FK column itself — they don't auto-create that index, and queries that join or check existence on it will scan otherwise.

Example

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

Exercise

Test yourself