FOREIGN KEY

Enforce referential integrity between tables with FOREIGN KEY constraints.

Overview

A foreign key (FK) is a constraint that ensures a column value in one table matches an existing value in another table's primary (or unique) key. It enforces referential integrity you cannot insert an order with a customer_id that doesn't exist in the customers table, and you cannot delete a customer who still has orders (unless you specify what happens on delete). Foreign keys define the relationships between tables. The table that holds the FK is the child (referencing) table; the table being referenced is the parent. The ON DELETE and ON UPDATE actions control what happens when the referenced row changes: CASCADE, SET NULL, SET DEFAULT, or RESTRICT (the default, which prevents the change).

Key Points

•REFERENCES parent_table(col) creates a FK constraint
•FK column must match the type of the referenced column
•ON DELETE CASCADE deletes child rows when parent is deleted
•ON DELETE SET NULL sets FK to NULL when parent is deleted
•ON DELETE RESTRICT (default) prevents deleting parent if children exist
•PostgreSQL creates an index on the FK column do it manually for performance

Examples

Inline and table-level foreign keys

CREATE TABLE customers (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id          SERIAL  PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  total       NUMERIC NOT NULL
);

-- Table-level syntax (useful for named constraints)
CREATE TABLE order_items (
  id         SERIAL  PRIMARY KEY,
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  CONSTRAINT fk_order   FOREIGN KEY (order_id)   REFERENCES orders(id),
  CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id)
);

FOREIGN KEY

Overview

Key Points

Examples

Exercises

Knowledge Check

What does ON DELETE CASCADE do?