DELETE CASCADE | PostgreSQL Tutorial

ON DELETE CASCADE is a referential action on a foreign key constraint. When a row in the parent table is deleted, PostgreSQL automatically deletes all rows in the child table that reference it. This keeps the database consistent without requiring manual cleanup queries.

Defining ON DELETE CASCADE

Add the ON DELETE CASCADE clause to the foreign key definition in the child table:

CREATE TABLE departments (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  id            SERIAL PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  department_id INT NOT NULL,
  FOREIGN KEY (department_id)
    REFERENCES departments(id)
    ON DELETE CASCADE
);

Each employee belongs to a department. The ON DELETE CASCADE clause on the department_id foreign key means that deleting a department row will automatically delete all employees in that department.

Observing the cascade effect

Insert sample data and then delete a department:

INSERT INTO departments (name) VALUES ('Engineering'), ('Sales');
INSERT INTO employees (name, department_id)
VALUES ('John Doe', 1), ('Jane Smith', 1), ('Michael Johnson', 2);

Delete the Engineering department (id = 1):

DELETE FROM departments WHERE id = 1;

Now query the employees table:

SELECT * FROM employees;

Output:

 id |      name       | department_id
----+-----------------+---------------
  3 | Michael Johnson |             2
(1 row)

John Doe and Jane Smith were automatically deleted because their department_id referenced the deleted Engineering row.

Practical tips

Use ON DELETE CASCADE when child rows have no meaningful existence without their parent — for example, order items without an order, or comments without a post.
Avoid cascade on tables where accidental parent deletion could silently remove large volumes of child data. Consider ON DELETE RESTRICT to prevent deletion if children exist.
Cascade actions can chain across multiple levels — if table C references table B which references table A, deleting from A cascades through B to C.
To add cascade to an existing foreign key, you must drop the constraint and recreate it with the ON DELETE CASCADE clause.
Always test cascade behavior in a non-production environment before enabling it on tables with significant data.

Reference: PostgreSQL documentation — Foreign Keys.

Frequently Asked Questions

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

ON DELETE CASCADE automatically deletes child rows when the parent is deleted. ON DELETE RESTRICT (the default) prevents deletion of the parent row if any child rows reference it, returning an error instead.

Can ON DELETE CASCADE chain across multiple tables?

Yes. If table B has ON DELETE CASCADE referencing table A, and table C has ON DELETE CASCADE referencing table B, then deleting a row in table A will cascade through B and then through C.

How do I add ON DELETE CASCADE to an existing foreign key?

You must drop the existing constraint and recreate it. For example: ALTER TABLE employees DROP CONSTRAINT employees_department_id_fkey; ALTER TABLE employees ADD CONSTRAINT employees_department_id_fkey FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE;

What other ON DELETE actions are available in PostgreSQL?

PostgreSQL supports: NO ACTION (default, error if child exists), RESTRICT (same as NO ACTION but checked immediately), CASCADE (delete child rows), SET NULL (set foreign key column to NULL), and SET DEFAULT (set foreign key column to its default value).

Is ON DELETE CASCADE safe to use in production?

It depends on your data model. It is safe and recommended when child rows are logically owned by the parent. It can be risky if a careless parent delete could silently wipe out large amounts of child data. Document the cascade relationships in your schema and test before enabling.