How to Use ON DELETE CASCADE in PostgreSQL: Syntax and Examples

In PostgreSQL, use ON DELETE CASCADE in a foreign key constraint to automatically delete rows in the child table when the referenced row in the parent table is deleted. This helps keep related data consistent without manual cleanup.

Syntax

The ON DELETE CASCADE clause is added to a foreign key constraint in PostgreSQL. It tells the database to delete child rows automatically when the parent row is deleted.

• FOREIGN KEY (column): Defines the child table column referencing the parent.
• REFERENCES parent_table(parent_column): Specifies the parent table and column.
• ON DELETE CASCADE: Enables automatic deletion of child rows when the parent row is deleted.

sql

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column)
REFERENCES parent_table(parent_column)
ON DELETE CASCADE;

Example

This example shows two tables: authors and books. The books table has a foreign key to authors with ON DELETE CASCADE. When an author is deleted, all their books are deleted automatically.

sql

CREATE TABLE authors (
  author_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  book_id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INT,
  CONSTRAINT fk_author
    FOREIGN KEY (author_id)
    REFERENCES authors(author_id)
    ON DELETE CASCADE
);

INSERT INTO authors (name) VALUES ('Jane Austen'), ('Mark Twain');
INSERT INTO books (title, author_id) VALUES
  ('Pride and Prejudice', 1),
  ('Emma', 1),
  ('Adventures of Huckleberry Finn', 2);

-- Delete author Jane Austen
DELETE FROM authors WHERE author_id = 1;

-- Check remaining books
SELECT * FROM books;

Output

 book_id |           title            | author_id 
---------+----------------------------+-----------
       3 | Adventures of Huckleberry Finn |         2
(1 row)

Common Pitfalls

Common mistakes when using ON DELETE CASCADE include:

• Forgetting to add ON DELETE CASCADE and expecting automatic deletion.
• Using it on the wrong foreign key, causing unintended data loss.
• Not understanding that cascading deletes can remove many rows, so use carefully.

Always test deletions on a development database before applying in production.

sql

/* Wrong: No ON DELETE CASCADE, child rows remain */
ALTER TABLE books
DROP CONSTRAINT IF EXISTS fk_author_no_cascade;
ALTER TABLE books
ADD CONSTRAINT fk_author_no_cascade
FOREIGN KEY (author_id)
REFERENCES authors(author_id);

/* Right: With ON DELETE CASCADE */
ALTER TABLE books
DROP CONSTRAINT IF EXISTS fk_author_cascade;
ALTER TABLE books
ADD CONSTRAINT fk_author_cascade
FOREIGN KEY (author_id)
REFERENCES authors(author_id)
ON DELETE CASCADE;

Quick Reference

Clause	Description
FOREIGN KEY (column)	Defines the child table column referencing the parent table.
REFERENCES parent_table(parent_column)	Specifies the parent table and column being referenced.
ON DELETE CASCADE	Automatically deletes child rows when the parent row is deleted.
ON DELETE SET NULL	Sets child foreign key to NULL when parent row is deleted (alternative).
ON DELETE RESTRICT	Prevents deletion of parent row if child rows exist.

Key Takeaways

Use ON DELETE CASCADE in foreign key constraints to auto-delete related child rows.

It helps maintain data integrity by cleaning up dependent rows automatically.

Be cautious: cascading deletes can remove many rows unexpectedly.

Always test cascading behavior in a safe environment before production.

ON DELETE CASCADE is part of the FOREIGN KEY definition referencing the parent table.