# Foreign Keys and REFERENCES

> Connect tables with foreign keys and choose the appropriate update and delete actions.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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

## Example {#example}

```sql {num="20.1" caption="Foreign Keys and REFERENCES"}
-- 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.

## Run the example {#run}

To run:

```text
$ 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 {#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 CASCADE`s, 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] 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.

> [!NOTE] 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.

## Downloads {#downloads}

SQL file: [foreign-keys.sql](/examples/source/foreign-keys.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)