SQL · Chapter 34 of 42

FOREIGN KEY

A FOREIGN KEY (FK) is a column that references a PRIMARY KEY in another table, enforcing referential integrity.

The database prevents inserting rows whose FK doesn't match a parent row.

ON DELETE / ON UPDATE

Choose behaviour when the parent changes: CASCADE, SET NULL, RESTRICT (default), NO ACTION.

Example 1 (sql)

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10,2)
);

Output

Delete a user, their orders vanish too

CASCADE on delete.

Example 2 (sql)

-- Attempt to insert an invalid FK:
INSERT INTO orders (user_id, total) VALUES (9999, 100);
-- ERROR: violates foreign key constraint

Output

ERROR

DB rejects orphan rows.

💡 Note: Databases don't auto-index foreign key COLUMNS (only the referenced PK). Add an index yourself for fast joins.