Schema Design and ConstraintsLesson 4.2

Primary keys, foreign keys, and referential integrity

PRIMARY KEY, FOREIGN KEY, referential integrity, ON DELETE CASCADE, ON DELETE RESTRICT, ON DELETE SET NULL, surrogate vs natural keys

Keys Define Identity and Relationships

A primary key uniquely identifies every row in a table. A foreign key is a column that references the primary key of another table, creating a relationship. The database can enforce that a foreign key value always points to an existing row — this is referential integrity.

CREATE TABLE customers (
  id    SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL
    REFERENCES customers(id)
    ON DELETE RESTRICT,      -- block deleting a customer who has orders
  amount      NUMERIC(10,2)
);

ON DELETE Behavior

RESTRICT (or NO ACTION): prevents deletion if a child row exists. Safe default.
CASCADE: deletes child rows automatically when the parent is deleted. Use carefully.
SET NULL: sets the foreign key column to NULL on parent deletion.

Surrogate vs Natural Keys

A surrogate key is an artificial id (SERIAL, UUID) with no business meaning. A natural key is a real-world identifier (email, ISBN). Use surrogate keys as primary keys — emails change, ISBNs have edge cases. Keep natural keys as UNIQUE constraints alongside the surrogate key.