PostgreSQL Foreign Key: ALTER TABLE, CASCADE Rules, and Indexing

You add a constraint to a table that has collected rows for two years, and PostgreSQL refuses it: somewhere in there sits a row whose parent was deleted long ago. A foreign key declares that every value in a child column exists in a parent column, and PostgreSQL checks that on every insert, update and delete, in both tables. Adding one to an existing table, choosing what a delete does, and indexing the child column look like this:

ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column)
ON DELETE CASCADE;

CREATE INDEX ON child_table (child_column);

CASCADE is one of five delete actions, and the index is yours to create, because PostgreSQL does not add one.

What a PostgreSQL foreign key does

Everything below runs on PostgreSQL 18, against two tables and five rows:

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL
);

INSERT INTO authors (author_id, name) VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO books (book_id, title, author_id) VALUES
    (10, 'Structured Design', 1),
    (11, 'Numerical Methods', 2),
    (12, 'Notes on Compilers', 9);

The books table carries no constraint yet, and book 12 points at an author id that was never inserted.

The table that stores the reference, books, is the child. The table that owns the referenced key, authors, is the parent. A foreign key on books.author_id keeps the two consistent: no book can point at an author that is not there. Once it exists, PostgreSQL checks four kinds of statement:

Deleting a book or inserting an author is never checked, because neither can leave a book without its author.

What PostgreSQL requires of the two columns

The parent side has to be unique. The PostgreSQL 18 documentation on constraints requires that "a foreign key must reference columns that either are a primary key or form a unique constraint, or are columns from a non-partial unique index". A column with an ordinary index behind it is rejected.

The two columns need comparable types rather than identical ones. A BIGINT column can reference authors.author_id, which is an INT, but a TEXT column cannot:

CREATE TABLE reviews (author_id TEXT REFERENCES authors (author_id));

ERROR:  foreign key constraint "reviews_author_id_fkey" cannot be implemented
DETAIL:  Key columns "author_id" of the referencing table and "author_id" of the referenced table are of incompatible types: text and integer.

A null in the child column is the one value that escapes the check, because "a referencing row need not satisfy the foreign key constraint if any of its referencing columns are null". Declaring the column NOT NULL, as the books table does, closes that gap.

PostgreSQL foreign key syntax

The short form goes on the column, and PostgreSQL makes up the constraint name:

author_id INT REFERENCES authors (author_id)

The name joins the table, the column and _fkey, so on the books table it becomes books_author_id_fkey. That name is hard to place when it turns up in an error or a migration script, so write the constraint out and name it yourself:

CONSTRAINT fk_books_author
FOREIGN KEY (author_id)
REFERENCES authors (author_id)
ON DELETE RESTRICT

In a new schema the same clause goes at the end of the child's column list:

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id)
        REFERENCES authors (author_id)
        ON DELETE RESTRICT
);

The parent table has to exist first, so create authors before books.

Validate existing data before adding a constraint

An orphan row does no visible harm until something joins through it. The query that lists books with their authors drops book 12 without a word:

SELECT b.book_id, b.title, a.name
FROM books b
JOIN authors a ON a.author_id = b.author_id
ORDER BY b.book_id;

The constraint would catch it, but adding it scans the whole table and fails on the first row with no parent:

ALTER TABLE books
ADD CONSTRAINT fk_books_author
FOREIGN KEY (author_id)
REFERENCES authors (author_id)
ON DELETE RESTRICT;

ERROR:  insert or update on table "books" violates foreign key constraint "fk_books_author"
DETAIL:  Key (author_id)=(9) is not present in table "authors".

The message names one row and stops. An anti-join finds all of them at once:

SELECT b.book_id, b.author_id
FROM books b
LEFT JOIN authors a
  ON a.author_id = b.author_id
WHERE a.author_id IS NULL;

Now you can decide what book 12 is: a typo, or the last trace of a deleted author. Where the child column allows nulls, add AND b.author_id IS NOT NULL to the query, since a null is not an orphan. The rest of this article assumes the row was repaired:

UPDATE books SET author_id = 2 WHERE book_id = 12;

Add a foreign key to an existing table

With the data clean, the same ALTER TABLE goes through, scanning every row on the way:

ALTER TABLE books
ADD CONSTRAINT fk_books_author
FOREIGN KEY (author_id)
REFERENCES authors (author_id)
ON DELETE RESTRICT;

On a large table that scan is the expensive part. NOT VALID skips it: ALTER TABLE says "this potentially-lengthy scan is skipped" while "the constraint will still be applied against subsequent inserts or updates". The old rows are checked later, when it suits you:

ALTER TABLE books
ADD CONSTRAINT fk_books_author
FOREIGN KEY (author_id)
REFERENCES authors (author_id)
ON DELETE RESTRICT
NOT VALID;

ALTER TABLE books
VALIDATE CONSTRAINT fk_books_author;

The split matters because of the locks each statement takes. An insert, update or delete needs a ROW EXCLUSIVE lock, and the lock table says which modes conflict with it:

So the scan moves into the statement that lets the application keep writing. Either way, once the constraint is in place a row with no parent never lands:

INSERT INTO books (book_id, title, author_id) VALUES (13, 'Lambda Papers', 99);

ERROR:  insert or update on table "books" violates foreign key constraint "fk_books_author"
DETAIL:  Key (author_id)=(99) is not present in table "authors".

From here on, the join in the previous section returns every book, because none of them can point at a missing author.

ON DELETE and ON UPDATE actions

The action decides what happens to the child rows when their parent row is deleted, or when its key changes:

NO ACTION and RESTRICT both reject the delete, and PostgreSQL 18 says which of the two stopped you:

DELETE FROM authors WHERE author_id = 1;

ERROR:  update or delete on table "authors" violates RESTRICT setting of foreign key constraint "fk_books_author" on table "books"
DETAIL:  Key (author_id)=(1) is referenced from table "books".

Under NO ACTION the same delete fails with "violates foreign key constraint" and no mention of RESTRICT. The other difference is timing: the documentation states that RESTRICT "does not allow the check to be deferred until later in the transaction", and NO ACTION does.

Updating the parent key meets the same check. fk_books_author declares no update action, so it takes the default, and moving author 1 to id 3 would leave book 10 behind:

UPDATE authors SET author_id = 3 WHERE author_id = 1;

ERROR:  update or delete on table "authors" violates foreign key constraint "fk_books_author" on table "books"
DETAIL:  Key (author_id)=(1) is still referenced from table "books".

ON UPDATE RESTRICT is stricter still: it "will prevent the update to run even if the state after the update would still satisfy the constraint".

Example with ON DELETE CASCADE

CASCADE turns one delete into several. Order items are the usual case, because an item that outlives its order is garbage:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    product_name TEXT NOT NULL,
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
        ON DELETE CASCADE
);

INSERT INTO orders (order_id, customer_id) VALUES (10, 1), (11, 2);
INSERT INTO order_items (item_id, order_id, product_name) VALUES
    (100, 10, 'Keyboard'),
    (101, 10, 'Mouse'),
    (102, 11, 'Monitor');

Delete order 10 and its two items go with it, in the same statement:

DELETE FROM orders WHERE order_id = 10;
SELECT item_id, order_id, product_name FROM order_items ORDER BY item_id;

Pick the action per relationship, not per schema. CASCADE on an order line is housekeeping; on a customer key it deletes the order history of anyone you remove. RESTRICT suits a parent row that must never disappear under a child, and SET NULL a column that is genuinely optional. Both SET NULL and SET DEFAULT "can take a column list to specify which columns to set", which matters for composite keys, and SET DEFAULT fails unless a parent row carries the default value.

What a foreign key buys, and what it costs

A foreign key moves a rule out of your application and into the database, where every writer meets it. A script, a second service or an UPDATE typed into psql gets the same refusal as your application, which is how rows like book 12 stop appearing. The actions go further and change the child rows for you, so a CASCADE replaces a cleanup job.

The planner reads the constraint as well. Since PostgreSQL 9.6 it compares the conditions of a join with the foreign keys between the tables, and the release notes say it "produces better estimates" of the rows the join returns.

The cost is the check itself. Each insert into books, and each change to a book's author, looks the author up through the primary key of authors. Each delete from authors, and each change to an author's key, has to find the books that still point at it. The tables also gain an order: parents are created and loaded before their children, and dropped after them.

Index the child column

PostgreSQL indexes the parent side for you, because the referenced columns already carry a primary key or a unique constraint. The child side is yours: "the declaration of a foreign key constraint does not automatically create an index on the referencing columns". Without one, every delete or key update in authors scans books to find the rows it has to check.

CREATE INDEX idx_books_author_id ON books (author_id);

Add the index when parent rows are deleted or their keys updated, and when you join the two tables on that column, which in an application schema is most of the time. Skip it on a lookup table nobody deletes from, where it costs every write and buys nothing. PostgreSQL Create Index and Indexes and Foreign Keys go through the choice in detail.

List existing foreign keys

A constraint you did not write is easiest to read from information_schema, which is standard SQL and works from any client:

SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column,
    tc.constraint_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON kcu.constraint_schema = tc.constraint_schema
 AND kcu.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_schema = tc.constraint_schema
 AND ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name, tc.constraint_name;

The joins match on the schema as well as the name, because the same constraint name in a second schema would otherwise multiply the rows. Inside psql, \d books prints the same constraints under the table's columns, along with its indexes and triggers. Describe Table in PostgreSQL reads that output field by field, and Show Tables in PostgreSQL covers the other catalog queries.

Drop a foreign key constraint

Dropping needs the constraint's name, which is the reason to choose one yourself:

ALTER TABLE books
DROP CONSTRAINT fk_books_author;

SELECT indexname FROM pg_indexes WHERE tablename = 'books' ORDER BY indexname;

The index on the child column survives. The statement "drops the specified constraint on a table, along with any index underlying the constraint", and a foreign key creates no index on the child, so drop idx_books_author_id yourself if nothing else needs it. In a migration that may run twice, DROP CONSTRAINT IF EXISTS turns the error into a notice.

Composite and self-referencing foreign keys

When the parent's identity spans two columns, the reference spans the same two, in the same order:

CREATE TABLE order_headers (
    order_id INT,
    branch_id INT,
    PRIMARY KEY (order_id, branch_id)
);

CREATE TABLE order_lines (
    line_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    branch_id INT NOT NULL,
    CONSTRAINT fk_order_lines_header
        FOREIGN KEY (order_id, branch_id)
        REFERENCES order_headers (order_id, branch_id)
);

The parent side still needs a primary key or a unique constraint over exactly that pair. Nulls behave differently here than in a one-column key: by default a line with a null branch_id escapes the check, whatever its order_id says. With MATCH FULL, "a referencing row escapes satisfying the constraint only if all its referencing columns are null", and declaring both columns NOT NULL, as above, closes the gap more simply.

The information_schema query in the previous section pairs every referencing column with every referenced column, so a two-column key comes back as four rows. pg_constraint gives one row per constraint:

SELECT conrelid::regclass AS table_name, conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'order_lines'::regclass;

Use a composite key only when the parent's identity really is composite. Where one surrogate key already identifies the header row, one column on each side is less to carry through every child table.

Self-referencing foreign keys

A table can reference itself, which the documentation calls a "self-referential foreign key". An employee's manager is another employee:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INT REFERENCES employees (employee_id)
);

INSERT INTO employees (employee_id, name, manager_id) VALUES
    (1, 'Edsger', NULL),
    (2, 'Barbara', 1),
    (3, 'Niklaus', 2);

The column allows nulls because the top of the hierarchy has no manager, so Edsger's row goes in with none. Every rule above applies unchanged: an employee pointing at a missing manager is refused, and deleting Edsger fails while Barbara still reports to him.

Model foreign keys in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

DDL tells you what one constraint does. A diagram tells you which tables a delete will reach, which is the question to answer before choosing CASCADE. DbSchema draws each foreign key as a line between two tables and lets you edit it there:

1. Connect through the PostgreSQL JDBC driver, and DbSchema reverse-engineers the schema onto an interactive diagram, with one line per foreign key.
2. Double-click a table on the diagram to open the DbSchema Table Editor, switch to the Foreign Keys tab, click Add, choose the referenced table, and map the columns. For a composite key, pair each referencing column with its referenced column in the same dialog.
3. Or drag from the connector handle on the referencing column to the target column, and DbSchema creates the foreign key and draws the line.
4. Double-click a relationship line to open the DbSchema Foreign Key Editor and set the delete and update actions: NO ACTION, CASCADE, SET NULL or SET DEFAULT.
5. Switch the notation DbSchema draws on the relationship lines from crow's foot to Barker when your team reads Barker diagrams.
6. Open Diagram → Export HTML5 or PDF Documentation in DbSchema to generate documentation that shows the relationships to people who never open the database.

Whether a change reaches the database depends on how DbSchema is working. Connected, DbSchema applies each schema change to the database as you make it. Disconnected, DbSchema saves the change only to the .dbs model file, and after you reconnect, the Synchronization Dialog lists each difference and applies it or generates a migration script. Virtual foreign keys stay in the model file on purpose: DbSchema uses them to draw a relationship the database does not declare.

Download DbSchema at https://dbschema.com/download.html, connect to your PostgreSQL database, and look at the lines around the table you were about to add a constraint to. Connecting, reverse-engineering and the diagram are in the free Community Edition. Saving the model to a .dbs file, the synchronization that writes a new constraint back to the database, and the HTML5 and PDF documentation are in Pro.

FAQ

Can a PostgreSQL foreign key reference a non-primary-key column?

Yes, as long as that column set carries a unique constraint or a non-partial unique index. An ordinary index is not enough, and PostgreSQL rejects the statement with "there is no unique constraint matching given keys for referenced table".

Can PostgreSQL defer a foreign key check until commit?

Only when the constraint was created DEFERRABLE. SET CONSTRAINTS ALL DEFERRED then holds the check until the transaction commits, which lets you insert a child row before its parent inside one transaction. RESTRICT is the exception, because its check is never deferred.