Mastering PostgreSQL Foreign Keys: Syntax, Actions, and Examples

Foreign keys in PostgreSQL link tables together, ensuring data consistency and preventing disconnected records. They're a key part of enforcing rules in a relational schema.

This guide will show you how to use them effectively, write the correct syntax, choose the right actions, and apply best practices.

Understanding Foreign Keys

A foreign key creates a link between two tables — typically, a child table that references a parent table. It ensures that any value in the foreign key column corresponds to a value in the parent table.

Basic Syntax

FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column)
ON DELETE action
ON UPDATE action

You can also optionally name the constraint using the CONSTRAINT keyword.

Example: Orders and Items

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer TEXT
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
);

If an order is deleted, the order_id in order_items becomes NULL.

Available Actions

• CASCADE: Automatically deletes or updates related rows

• SET NULL: Clears the foreign key

• SET DEFAULT: Sets the key to a default value

• NO ACTION or RESTRICT: Prevents changes if dependencies exist

Practical Example: ON DELETE CASCADE

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

Deleting a customer deletes all their invoices.

FAQ

Why are foreign keys important?

They help enforce relational integrity and ensure that records in one table always relate to valid entries in another.

What’s the difference between CASCADE and SET NULL?

CASCADE deletes child rows. SET NULL clears the foreign key without deleting the row.

Can I use multiple columns as a foreign key?

Yes. You can define a foreign key on a combination of columns if the referenced parent table also has a matching unique constraint or primary key.

Is RESTRICT the same as NO ACTION?

Functionally, yes. Both prevent changes that would break the relationship. RESTRICT is more immediate and explicit.

Conclusion

PostgreSQL foreign keys are an essential part of relational design. They maintain logical consistency and define how rows in different tables relate.

Using actions like CASCADE, SET NULL, and RESTRICT, you can tightly control how data behaves when rows are deleted or updated.

For managing complex relationships visually, tools like DbVisualizer can simplify your workflow and improve clarity.

Read a Definitive Guide to Postgres Foreign Key for more insights.