Postgres by Example: Foreign Keys and REFERENCES

A foreign key is a constraint that says "the value in this column must already exist as a primary key (or unique key) in some other table." It is how relational databases enforce referential integrity — you cannot orphan a child row by pointing it at a non-existent parent, and (depending on the rules you choose) the database can cascade or restrict updates to the parent. Every relational model that involves multiple tables needs foreign keys.

What you'll learn:

• Declaring a foreign key with REFERENCES
• The ON DELETE and ON UPDATE actions: NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT
• Why an index on the FK column is almost always a good idea
• How DEFERRABLE constraints let you batch changes inside a transaction
• Inspecting foreign keys in the catalog

-- Parent table
DROP TABLE IF EXISTS comments_example;
DROP TABLE IF EXISTS authors_example;

CREATE TABLE authors_example (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

-- Child table referencing parent
CREATE TABLE comments_example (
  id        integer PRIMARY KEY,
  author_id integer NOT NULL REFERENCES authors_example(id) ON DELETE CASCADE,
  body      text NOT NULL,
  parent_id integer REFERENCES comments_example(id) ON DELETE SET NULL  -- self-reference!
);

-- Index the FK column so joins and cascading deletes don't seq-scan
CREATE INDEX ON comments_example (author_id);
CREATE INDEX ON comments_example (parent_id);

INSERT INTO authors_example VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO comments_example (id, author_id, body) VALUES
  (1, 1, 'First!'),
  (2, 2, 'Reply'),
  (3, 1, 'Nice');
UPDATE comments_example SET parent_id = 1 WHERE id = 2;

-- Refused: author 99 does not exist
-- INSERT INTO comments_example (id, author_id, body) VALUES (4, 99, 'orphan');

-- Cascade: deleting Alice removes her comments (and Bob's reply loses its parent_id)
DELETE FROM authors_example WHERE id = 1;
SELECT * FROM comments_example ORDER BY id;

-- Inspect foreign keys
SELECT conname, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'comments_example'::regclass;

REFERENCES other_table(column) declares the foreign key. The referenced column must be the primary key or a unique constraint of the other table. If you omit the column name, the referenced primary key is assumed: REFERENCES authors_example is equivalent to REFERENCES authors_example(id) here.

The ON DELETE and ON UPDATE actions decide what happens when the parent row's key changes:

• NO ACTION (default): error if any child still references the parent. Checked at the end of the statement.
• RESTRICT: like NO ACTION but checked immediately (cannot be deferred).
• CASCADE: delete or update the child rows too. Useful when the child rows are owned by the parent.
• SET NULL: set the child column to NULL (the column must be nullable).
• SET DEFAULT: set the child column to its default.

Foreign keys are checked at every INSERT/UPDATE/DELETE — they are not free. They are also one of the most important correctness features in your schema; the cost almost always pays for itself in avoided data corruption.

To run:

$ psql -f source/foreign-keys.sql postgres
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 2
INSERT 0 3
UPDATE 1
DELETE 1
 id | author_id | body  | parent_id
----+-----------+-------+-----------
  2 |         2 | Reply |
(1 row)
 ...

Common pitfalls:

• Forgetting an index on the FK column: when you delete a parent row, PostgreSQL must check (and possibly cascade to) child rows; without an index on the FK column this is a sequential scan. Always index the FK column.
• Choosing CASCADE too eagerly: a deletion in production can cascade across millions of rows. Audit your ON DELETE CASCADEs, especially on user-facing tables.
• Importing data with cross-table dependencies: load the parent first, then the child. Or use DEFERRABLE INITIALLY DEFERRED so the FK check happens at commit instead of per-row.
• Cycles between FKs require deferrable constraints. PostgreSQL supports them, but design around the need when possible.

Tip: DEFERRABLE INITIALLY DEFERRED makes a constraint check at COMMIT rather than statement end. Combined with SET CONSTRAINTS ALL DEFERRED, this lets you make several inter-related changes inside a transaction without ordering them perfectly.

Try it: Try to insert a comment with a non-existent author: INSERT INTO comments_example (id, author_id, body) VALUES (10, 99, 'orphan'); and observe the FK violation. Then add a second author and a comment, and use DELETE on the author to see ON DELETE CASCADE in action.

Source: foreign-keys.sql

Next: NOT NULL and DEFAULT

Home: Postgres by Example