FOREIGN KEY

This Topic's Focus

Chapter 5 explained referential integrity conceptually. This topic is about the SQL mechanics: declaration syntax, naming, the ON DELETE/ON UPDATE action clauses, and how validation behaves when you add a foreign key to a table that already has data.

Column-Level Syntax

sql
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

Table-Level Syntax (required for composite foreign keys)

sql
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT,
    FOREIGN KEY (order_id, product_id)
        REFERENCES order_catalog(order_id, product_id)
);

Just like PRIMARY KEY, a foreign key referencing more than one column in the parent table can only be written using the table-level FOREIGN KEY (...) REFERENCES ... clause — you cannot inline a multi-column reference onto a single column.

Naming the Constraint

sql
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ON DELETE / ON UPDATE Actions

sql
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customer
      FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

Common action keywords: CASCADE (propagate the delete/update to child rows), SET NULL (child FK column becomes NULL), RESTRICT/NO ACTION (block the parent operation if children reference it — the default in most engines), SET DEFAULT (child FK reverts to its DEFAULT value).

Adding a FOREIGN KEY to an Existing Populated Table

sql
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

This scans every existing row in orders; if any customer_id value has no matching row in customers (an "orphan"), the statement fails. The standard fix is to find and clean up orphans first:

sql
SELECT o.* FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL AND o.customer_id IS NOT NULL;

Dropping It

sql
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;   -- PostgreSQL/Oracle/SQL Server
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;  -- MySQL

Edge Cases

• The referenced parent column(s) must be a PRIMARY KEY or have a UNIQUE constraint — you cannot reference an arbitrary non-unique column.
• A NULL value in the FK column is always allowed regardless of whether it matches any parent row — foreign keys don't enforce NOT NULL by themselves; combine explicitly with NOT NULL if required.
• Some engines (notably older MySQL MyISAM tables) silently ignore FOREIGN KEY clauses entirely because the storage engine doesn't support them — always confirm InnoDB is in use.

Key Takeaways / Q&A

Q: When is table-level FOREIGN KEY syntax mandatory? A: When the key references multiple columns in the parent table as a composite reference.

Q: What happens if you add a FOREIGN KEY constraint to a table that already contains orphaned rows? A: The ALTER TABLE statement fails; orphans must be fixed or removed first.

Q: Does a FOREIGN KEY column reject NULL by default? A: No — NULL is exempt from FK matching; add NOT NULL separately if the relationship must always be populated.