PostgreSQL Foreign Key – ALTER TABLE, CASCADE Rules, and Indexing | DbSchema

Table of Contents

1. What a PostgreSQL foreign key does
2. PostgreSQL foreign key syntax
3. Create tables with a foreign key
4. ON DELETE and ON UPDATE actions
5. Validate existing data before adding a constraint
6. Add a foreign key to an existing table
7. Composite foreign keys in PostgreSQL
8. Drop a foreign key constraint
9. List existing foreign keys
10. Best practices for PostgreSQL foreign keys
11. Model foreign keys in DbSchema
12. FAQ
13. Conclusion

A PostgreSQL foreign key enforces a relationship between a child table and a parent table. It makes sure values in the child table point to valid rows in the referenced table, which protects referential integrity and makes joins easier to trust.

This page focuses on PostgreSQL-specific DDL and maintenance patterns such as ALTER TABLE, NOT VALID, indexing, and inspection queries. If you want the database-agnostic concept first, start with What Is a Foreign Key in SQL?.

What a PostgreSQL foreign key does

A PostgreSQL foreign key connects:

• the parent table that owns the referenced key
• the child table that stores the reference

The referenced column is usually a primary key, but PostgreSQL can also reference a column or column set protected by a UNIQUE constraint.

In practice, PostgreSQL foreign keys ensure that:

• every book.author_id points to a real author
• every order.customer_id points to a real customer
• parent deletes and updates follow explicit rules
• schema documentation matches what the database actually enforces

That last point matters more than many tutorials admit. When relationships are enforced and also shown in a DbSchema diagram, the schema becomes easier to review, explain, and evolve safely.

PostgreSQL foreign key syntax

You can define a foreign key inline:

author_id INT REFERENCES authors(author_id)

For production schemas, the full named-constraint form is usually clearer:

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

PostgreSQL also supports advanced options such as DEFERRABLE, but the core rule is the same: the child value must match a valid row in the referenced table.

Named constraints are easier to inspect, document, drop, and recreate later. They also read better in migration history.

Create tables with a foreign key

Here is a complete PostgreSQL example with a parent table and a child table:

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,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id)
        REFERENCES authors(author_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

This means:

• a book cannot point to an author that does not exist
• deleting an author fails while dependent books still exist
• if the parent key changes, the child key can update with it

If you are building the schema from scratch, How to Create a Table in PostgreSQL is the natural companion page.

ON DELETE and ON UPDATE actions

These actions are some of the most searched PostgreSQL foreign-key topics.

Example with ON DELETE CASCADE

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
);

If you delete an order:

DELETE FROM orders
WHERE order_id = 10;

PostgreSQL also deletes the matching rows from order_items.

Choose actions intentionally

• Use CASCADE when the child row should vanish with the parent
• Use SET NULL when the relationship is optional
• Use RESTRICT or NO ACTION when application logic should handle cleanup explicitly

Validate existing data before adding a constraint

One of the most common PostgreSQL foreign-key problems is trying to add a constraint to a table that already contains orphan rows.

This query finds books.author_id values that do not match any author:

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

Run a check like this before ALTER TABLE ... ADD CONSTRAINT. It is faster to clean the data first than to discover the error mid-migration.

If you inherit a large schema, DbSchema helps here by showing the related tables visually while you inspect the live data and fix the missing references.

Add a foreign key to an existing table

The normal pattern is:

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

For large tables or phased rollouts, PostgreSQL also supports NOT VALID:

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

ALTER TABLE books
VALIDATE CONSTRAINT fk_books_author;

This lets you create the constraint definition first and validate existing rows separately.

Before you add any PostgreSQL foreign key, make sure:

• the child and parent data types match
• the referenced column is backed by a primary key or UNIQUE constraint
• you have checked for orphan rows
• you understand the delete and update rules

Composite foreign keys in PostgreSQL

PostgreSQL supports composite foreign keys when the referenced identity spans multiple columns:

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)
);

Use a composite foreign key only when the parent identity is truly composite. If one surrogate key already identifies the row, that simpler approach is usually easier to maintain.

Drop a foreign key constraint

To remove a PostgreSQL foreign key:

ALTER TABLE books
DROP CONSTRAINT fk_books_author;

This is one reason named constraints are valuable: you can manage them directly instead of hunting for system-generated names.

List existing foreign keys

To inspect foreign keys already defined in a PostgreSQL database, query information_schema:

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;

In psql, you can also inspect a table quickly with:

\d books

If you prefer a visual view, DbSchema shows the same relationships in the diagram and in generated schema documentation.

Best practices for PostgreSQL foreign keys

1. Name constraints clearly. fk_books_author is easier to maintain than a generated name.

2. Index child columns when appropriate. PostgreSQL does not automatically index the child foreign-key column. Add an index when joins, deletes, or updates will hit that column often. PostgreSQL Create Index and Indexes and Foreign Keys go deeper.

3. Match data types exactly. Parent and child columns should align in both type and meaning.

4. Choose cascade behavior intentionally. CASCADE is convenient, but dangerous if you have not traced the consequences.

5. Use DEFERRABLE only when transaction logic really needs it. Most schemas do not need deferred constraint checks.

6. Document the relationship. The combination of PostgreSQL DDL plus DbSchema documentation is easier for teams to review than DDL alone. See the foreign keys documentation and schema documentation.

If you are also reverse-engineering tables, PostgreSQL Describe Table and List All Schemas in PostgreSQL are useful follow-ups.

Model foreign keys in DbSchema

DbSchema is especially useful for PostgreSQL foreign keys because relationships are easier to validate visually than through raw DDL alone.

You can:

1. connect through the PostgreSQL JDBC driver
2. reverse-engineer an existing schema or design one from scratch
3. create foreign keys visually in the table editor
4. see parent-child relationships directly in the diagram
5. generate or synchronize the resulting SQL
6. publish documentation so teammates can review the same model

This makes it much easier to spot missing indexes, unclear relationship names, or unintended cascade paths before they become production problems.

FAQ

How do I add a foreign key to an existing table in PostgreSQL?

Use ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES .... If the table is large, PostgreSQL also supports NOT VALID followed by VALIDATE CONSTRAINT.

What is the difference between ON DELETE CASCADE and ON DELETE RESTRICT?

CASCADE deletes child rows automatically. RESTRICT blocks the parent delete while child rows still exist.

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

Yes, if the referenced column or column set has a valid UNIQUE constraint.

Should I index foreign-key columns?

Often yes. PostgreSQL does not create child-side indexes automatically.

Can PostgreSQL foreign keys use multiple columns?

Yes. PostgreSQL supports composite foreign keys when the parent identity is also composite.

Can PostgreSQL defer a foreign key check until commit?

Yes, if the constraint is created as DEFERRABLE. That is useful for specific transactional workflows, but many schemas do not need it.

Conclusion

A PostgreSQL foreign key is one of the most important tools for protecting relational integrity. Once you understand the syntax, cascade actions, ALTER TABLE patterns, and indexing considerations, it becomes much easier to build reliable schemas.

Use DbSchema to model those relationships visually, inspect existing foreign keys, and generate PostgreSQL DDL with fewer surprises.