Foreign keys and referential integrity in SQL

Foreign Keys Enforce Relationships

A foreign key (FK) tells the database that a column's values must exist in a referenced table's primary key. Insert an order with a non-existent customer_id and the database rejects it — no application code needed.

ON DELETE Behavior

-- RESTRICT (default): block deletion of a customer that has orders
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE RESTRICT

-- CASCADE: delete all orders when the customer is deleted
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE CASCADE

-- SET NULL: orphan the order (customer_id becomes NULL)
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE SET NULL

Choosing the Right Action

• RESTRICT — use when child records must not be orphaned (orders, invoices).
• CASCADE — use for dependent data that has no meaning without the parent (user sessions, log entries).
• SET NULL — use when the relationship is optional (a post's author can be deleted while keeping the post).

Always add an index on foreign key columns. MySQL creates them automatically; PostgreSQL does not. A missing FK index turns every parent-row delete into a full table scan.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);