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. PostgreSQL checks that on every insert, update and delete, in both tables.

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.

What a PostgreSQL foreign key does

A foreign key ties a child table, which stores the reference, to a parent table, which owns the referenced key. The parent side is not free-form: 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.

Once the constraint exists, an insert whose author id is absent from the parent table is refused, an update that changes the child value to an absent id is refused, and a delete of a parent row that still has children follows the action you declared.

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 above does, closes that gap.

PostgreSQL foreign key syntax

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

author_id INT REFERENCES authors (author_id)

The name it invents joins the table, the column and _fkey, so the constraint above ends up as books_author_id_fkey. A name like that is hard to place when it turns up in an error or in 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
ON UPDATE CASCADE

In a fresh schema the same clause sits at the end of the column list, in the CREATE TABLE of the child:

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
        ON UPDATE CASCADE
);

The parent table has to exist first, so the order of the statements in the script matters.

Validate existing data before adding a constraint

An ALTER TABLE ... ADD CONSTRAINT scans the whole table and fails if a single row has 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;

One orphan, and now you can decide what it is: a typo, or a parent row that was deleted. Where the child column allows nulls, add AND b.author_id IS NOT NULL to the query: a null is not an orphan.

Point book 12 at an author that exists, or delete it. 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 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 second statement is the one you schedule for a quiet hour. It "acquires a SHARE UPDATE EXCLUSIVE lock", which lets reads and writes continue, while ADD FOREIGN KEY takes a SHARE ROW EXCLUSIVE lock on both tables and blocks writes for as long as it runs.

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".

ON DELETE and ON UPDATE actions

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

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 second difference is timing: a NO ACTION check can be held until the transaction commits, and a RESTRICT check cannot. Updating the parent key hits the same check. fk_books_author declares no update action, so it takes the default:

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".

After the update no author 1 is left for book 10 to point at. ON UPDATE NO ACTION "will allow the update to proceed and the foreign-key constraint will be checked against the state after the update", which is the check that just failed. ON UPDATE RESTRICT is stricter, and "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 rather than per schema. CASCADE on an order line is housekeeping; the same setting on a customer key deletes the order history of anyone you remove. RESTRICT is the choice where a parent row must never disappear under a child. SET NULL fits a column that is genuinely optional, and it can name the columns to clear: the documentation notes that "the actions SET NULL and SET DEFAULT can take a column list to specify which columns to set", which matters for composite keys where only part of the reference should go. SET DEFAULT needs a parent row that carries the default, since "if an action specifies SET DEFAULT but the default value would not satisfy the foreign key constraint, the operation will fail".

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 the parent table scans the child table to find the rows it has to check.

CREATE INDEX idx_books_author_id ON books (author_id);

Add the index when the parent rows are deleted or their keys updated, and when you join the two tables on that column, which is most of the time in an application schema. Skip it on a lookup table nobody deletes from, where the index costs writes 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 out of 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 tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name, tc.constraint_name;

Inside psql, \d books prints the same constraints under the table's columns, along with the indexes and triggers; Describe Table in PostgreSQL reads that output field by field, and Show Tables in PostgreSQL covers the rest of the catalog queries.

Drop a foreign key constraint

Dropping needs the constraint name, which is the argument for naming it in the first place:

ALTER TABLE books
DROP CONSTRAINT fk_books_author;

The statement "drops the specified constraint on a table, along with any index underlying the constraint", so an index you created separately for the child column stays behind and has to be dropped on its own. Adding IF EXISTS turns the error into a notice, which is what you want in a migration script that may run twice.

Composite foreign keys in PostgreSQL

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 has to be a primary key or a unique constraint over exactly that pair of columns. Nulls behave differently here than in a single-column key: by default a line with a null branch_id escapes the check even though order_id points at a real header. MATCH FULL closes that, since with it "a referencing row escapes satisfying the constraint only if all its referencing columns are null", and declaring both columns NOT NULL closes it more simply.

The information_schema query above pairs every referencing column with every referenced column, so a two-column key comes back as four rows. pg_catalog gives one row per constraint instead:

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

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

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 you have before choosing CASCADE. DbSchema draws the constraints as lines between the tables and lets you edit them there:

1. Connect through the PostgreSQL JDBC driver and let DbSchema reverse-engineer 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. A composite key pairs 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 immediately.
4. Double-click a relationship line to open the DbSchema Foreign Key Editor and set the delete and update actions there, from NO ACTION, CASCADE, SET NULL and SET DEFAULT.
5. Switch the notation DbSchema draws from Diagram → FK Notation when your team reads Crow's Foot, Barker or UML.
6. Generate schema documentation so the relationships are readable by people who do not open the database.

Which of those changes the database depends on how you are working. Connected, a foreign key you create on the diagram is executed against the database as you make it. Disconnected, it is written only to the .dbs model file, and it reaches the database when you run Schema → Synchronize Model with Database and execute the generated SQL. Virtual foreign keys are the third case: they stay in the model file on purpose, which is how you 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 and the synchronization that writes the new constraint back to the database 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 ALTER TABLE with a message saying there is no unique constraint matching the given keys.

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 is what lets you insert a child row before its parent inside one transaction. RESTRICT is the exception: the documentation states that it "does not allow the check to be deferred until later in the transaction".