SQL / DEFINING TABLES AND CONSTRAINTS
Foreign keys and keeping references honest
Declare foreign keys that force child rows to point at existing parent rows, choose whether the reference is optional, and read violation errors precisely.
What you will learn
- Write REFERENCES parent (col) so every child value must match a live parent row
- Put a PRIMARY KEY or UNIQUE on the referenced columns before referencing them
- Add NOT NULL when the link is mandatory; a NULL foreign key skips the check
- Name the constraint yourself so violation messages identify the relationship
Understanding Foreign keys and keeping references honest
A foreign key is a promise about the values in a column: whatever sits in books.author_id must already sit in authors.author_id. The engine enforces that promise in both directions, so an insert or update on the child naming a missing parent is refused, and a delete or key change on the parent that would strand a child is refused too. The mental model that helps is that the constraint is not a description of your data but a check the database runs on your behalf at every write touching either table. The error text quoted here is PostgreSQL's; other engines refuse the same writes with different wording.
The referenced side must carry a primary key or unique constraint over exactly the columns you name. "Points at a parent row" only means something if the value identifies at most one row: if author_id could repeat in authors, a child value would name a set rather than a row and there would be nothing definite to keep honest. That is why PostgreSQL rejects the child table's definition immediately rather than waiting for bad data. The child column's type should also match the parent key's type, because verification is a value comparison, not a guess.
NULL is the deliberate gap, not a violation: a NULL child column states no reference at all, so the check has nothing to look up and the row is accepted. A mandatory relationship therefore needs two declarations, NOT NULL and the foreign key together. The check also applies backwards in time when you create it, since adding a foreign key to a populated table scans the rows already there and fails if even one is an orphan. And a foreign key only guarantees that the referenced row exists, never that it is the correct one.
CREATE TABLE authors (
author_id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE books (
book_id integer PRIMARY KEY,
title text NOT NULL,
author_id integer NOT NULL
CONSTRAINT books_author_fk REFERENCES authors (author_id)
);
INSERT INTO authors VALUES (1, 'Ursula Le Guin'), (2, 'Italo Calvino');
INSERT INTO books VALUES (10, 'The Dispossessed', 1);
-- child pointing at an author that does not exist
INSERT INTO books VALUES (11, 'Untitled', 99);
-- parent that a child still references
DELETE FROM authors WHERE author_id = 1;A foreign key makes the database verify, on every write to either table, that a child's reference names a parent row that actually exists.
Worked examples
An optional reference
Shows that a NULL foreign key column passes the check while a wrong value does not.
CREATE TABLE departments (
dept_id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE employees (
emp_id integer PRIMARY KEY,
name text NOT NULL,
dept_id integer REFERENCES departments (dept_id)
);
INSERT INTO departments VALUES (1, 'Design');
INSERT INTO employees VALUES (1, 'Rita', 1);
INSERT INTO employees VALUES (2, 'Sam', NULL);
INSERT INTO employees VALUES (3, 'Ash', 4);Example explained
Line 1The unnamed REFERENCES clause makes PostgreSQL generate employees_dept_id_fkey, which is the name you then have to read in the error.
Line 2Sam's NULL dept_id gives the constraint nothing to look up, so an unassigned employee is stored without complaint.
Line 3Ash names department 4, which no departments row holds, so that statement is rejected whole and stores nothing.
Line 4If an employee without a department should be impossible, dept_id needs NOT NULL as well; the foreign key never implies it.
A composite reference matched as a unit
Shows why the parent columns need uniqueness and how a two-column key is checked as one tuple.
CREATE TABLE cities (
country_code char(2),
city_name text
);
CREATE TABLE addresses (
address_id integer PRIMARY KEY,
country_code char(2),
city_name text,
FOREIGN KEY (country_code, city_name) REFERENCES cities (country_code, city_name)
);
ALTER TABLE cities ADD PRIMARY KEY (country_code, city_name);
CREATE TABLE addresses (
address_id integer PRIMARY KEY,
country_code char(2),
city_name text,
FOREIGN KEY (country_code, city_name) REFERENCES cities (country_code, city_name)
);
INSERT INTO cities VALUES ('IT', 'Torino'), ('FR', 'Paris');
INSERT INTO addresses VALUES (1, 'FR', 'Torino');Example explained
Line 1The first CREATE TABLE fails on definition, not on data: nothing guarantees that a (country_code, city_name) pair appears once in cities.
Line 2ALTER TABLE cities ADD PRIMARY KEY over both columns supplies that uniqueness, and the identical CREATE TABLE then succeeds.
Line 3('FR', 'Torino') is refused although 'FR' and 'Torino' each exist in cities, because the pair is looked up as a single key.
Line 4Declaring two separate one-column foreign keys instead would have accepted that invented combination.
A table that references itself
Shows a foreign key whose parent and child are the same table, and how the root row is expressed.
CREATE TABLE staff (
staff_id integer PRIMARY KEY,
name text NOT NULL,
manager_id integer REFERENCES staff (staff_id)
);
INSERT INTO staff VALUES (1, 'Nadia', NULL);
INSERT INTO staff VALUES (2, 'Owen', 1);
INSERT INTO staff VALUES (3, 'Priya', 9);Example explained
Line 1REFERENCES staff (staff_id) inside staff points the table at its own primary key, so every manager_id must be an existing staff_id.
Line 2Nadia's NULL manager_id is how the top of the hierarchy is stated: there is no row above her to reference.
Line 3Owen may reference 1 because that row was already stored; with one insert per statement, managers have to be entered before their reports.
Line 4PostgreSQL checks references at the end of each statement, so a single multi-row INSERT can reference rows that same statement adds.
Important notes
Refusing to delete or re-key a referenced parent row is the default reaction; cascading or nulling out the children is a separate behaviour you have to request explicitly.
Adding a foreign key to a table that already holds rows validates those rows, so a retrofit keeps failing until you locate and repair the existing orphans.
Common mistakes
Referencing a parent column that has no PRIMARY KEY or UNIQUE: the child's CREATE TABLE fails outright with 'no unique constraint matching given keys', which beginners misread as a broken FOREIGN KEY syntax.
Splitting a two-column reference into two single-column foreign keys: each column is validated on its own, so pairs that never existed in the parent, like ('FR', 'Torino'), are accepted.
Writing REFERENCES in SQLite without running PRAGMA foreign_keys = ON for the connection: the clause parses and is stored, nothing is enforced, and orphan rows pile up unnoticed.
Try it yourself
Change, predict, then run
Create genres(genre_id integer PRIMARY KEY, name text NOT NULL) and tracks(track_id integer PRIMARY KEY, title text NOT NULL, genre_id integer NOT NULL REFERENCES genres (genre_id)), then insert one genre and one valid track. Now insert a track with genre_id 42 and delete the genre row that the valid track uses, and compare the two error messages.
Open the SQL workspaceCheck your understanding
A shipments table declares order_id integer REFERENCES orders (order_id) and nothing else about that column. Which of these writes will the database still allow?
- A shipments row whose order_id is 500 when no order 500 exists
- Deleting the orders row that an existing shipment points at
- A shipments row whose order_id is NULL
- Updating a shipment's order_id from 7 to 500 when no order 500 exists
Show answer
A foreign key constrains only non-NULL values: NULL says the row names no parent at all, so there is nothing to look up and the insert succeeds; forbidding it requires a separate NOT NULL. Deleting the referenced order looks harmless because the change happens on the parent side, but the constraint is checked in that direction too, and the delete is refused while a shipment still references that row.