DELETE CASCADE in PostgreSQL

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. The cascade fires within the same transaction as the parent delete, so it is atomic and rollback-safe.

Syntax

Define ON DELETE CASCADE on the foreign key in the child table:

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

CREATE TABLE child_table (
  id        SERIAL PRIMARY KEY,
  parent_id INT NOT NULL,
  data      TEXT,
  FOREIGN KEY (parent_id)
    REFERENCES parent_table(id)
    ON DELETE CASCADE
);

To add cascade to a column-level foreign key shorthand:

CREATE TABLE child_table (
  id        SERIAL PRIMARY KEY,
  parent_id INT NOT NULL REFERENCES parent_table(id) ON DELETE CASCADE,
  data      TEXT
);

Practical Example

Model a project management schema where deleting a project also removes all its tasks:

CREATE TABLE projects (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  owner       VARCHAR(100) NOT NULL,
  archived    BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE tasks (
  id          SERIAL PRIMARY KEY,
  project_id  INT NOT NULL,
  title       VARCHAR(200) NOT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  FOREIGN KEY (project_id)
    REFERENCES projects(id)
    ON DELETE CASCADE
);

CREATE TABLE task_comments (
  id       SERIAL PRIMARY KEY,
  task_id  INT NOT NULL,
  author   VARCHAR(100) NOT NULL,
  body     TEXT NOT NULL,
  FOREIGN KEY (task_id)
    REFERENCES tasks(id)
    ON DELETE CASCADE
);

Insert sample data:

INSERT INTO projects (name, owner)
VALUES ('Website Redesign', 'Alice'), ('API v2', 'Bob');

INSERT INTO tasks (project_id, title, status)
VALUES
  (1, 'Wireframes',    'done'),
  (1, 'Frontend build','in_progress'),
  (2, 'Auth endpoints','pending');

INSERT INTO task_comments (task_id, author, body)
VALUES
  (1, 'Carol', 'Approved the layout'),
  (2, 'Dan',   'Working on responsive breakpoints');

Delete the Website Redesign project:

DELETE FROM projects WHERE id = 1;

Output:

DELETE 1

Because tasks has ON DELETE CASCADE referencing projects, and task_comments has ON DELETE CASCADE referencing tasks, all related tasks and their comments are automatically removed:

SELECT * FROM tasks;

 id | project_id |     title      | status
----+------------+----------------+---------
  3 |          2 | Auth endpoints | pending
(1 row)

SELECT * FROM task_comments;

 id | task_id | author | body
----+---------+--------+------
(0 rows)

ON DELETE Referential Actions Compared

Testing with Vela

Cascade behavior is invisible until a parent row is actually deleted — and by then it is too late to undo without a backup. Use Vela database branching to create a production-like branch, delete a parent row, and verify the full cascade chain across all child tables before enabling ON DELETE CASCADE in production. Branching lets you trace exactly how far a delete propagates without any risk to live data.

Production Tips

• Use ON DELETE CASCADE only when child rows have no meaningful existence without their parent — for example, order line items, task comments, or audit events tied to a single entity.
• Use ON DELETE RESTRICT or NO ACTION when you want an error rather than silent propagation — it forces application code to handle cleanup explicitly.
• Cascades can chain through multiple levels and generate significant I/O on large datasets. Index the foreign key columns in child tables so PostgreSQL can find child rows efficiently.
• To add ON DELETE CASCADE to an existing constraint: ALTER TABLE child DROP CONSTRAINT fk_name; ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(id) ON DELETE CASCADE;
• The cascade runs inside the same transaction as the parent delete — if the parent delete is rolled back, all cascaded deletes are rolled back too.
• Document cascade relationships in your schema so future engineers understand the data destruction scope of a single parent DELETE.