Introduction to the PostgreSQL DELETE CASCADE

In PostgreSQL, theDELETE CASCADE is a referential action that allows you to automaticallydelete related rows in child tables when a parent row is deleted from the parent table.

This feature helps you maintain referential integrity in the database by ensuring that dependent rows are removed when their corresponding rows are deleted.

To enable theDELETE CASCADE action, you need to have two related tablesparent_table andchild_table:

CREATE TABLE parent_table(    idSERIAL PRIMARY KEY,    ...);CREATE TABLE child_table(    idSERIAL PRIMARY KEY,    parent_idINT,    FOREIGN_KEY(parent_id)       REFERENCES parent_table(id)       ON DELETE CASCADE);

In the child table, theparent_id is a foreign key that references theid column of theparent_table.

TheON DELETE CASCADE is the action on theforeign key that will automatically delete the rows from thechild_table whenever corresponding rows from theparent_table are deleted.

Let’s take a look at an example.

PostgreSQL DELETE CASCADE example

First, create tablesdepartments andemployees to store departments and employees:

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

In this setup, a department may have one or more employees and each employee belongs to a department.

In theemployees table, the department_id is a foreign key that references the id column of thedepartments table.

The foreign key has theON DELETE CASCADE clause that specifies the referential action to take when a row in thedepartments table is deleted.

Second,insert rows intodepartments andemployees tables:

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

Output:

id |    name----+-------------  1 | Engineering  2 | Sales(2 rows) id |      name       | department_id----+-----------------+---------------  1 | John Doe        |             1  2 | Jane Smith      |             1  3 | Michael Johnson |             2(3 rows)

Third, delete a department and observe the cascading effect on associated employees:

DELETE FROM departmentsWHERE id= 1;

Once you execute this statement, it deletes all employees belonging to the department withdepartment_id = 1 due to theDELETE CASCADE action defined on the foreign key constraint.

Finally, retrieve data from theemployees table to verify the employees associated with the deleted department:

SELECT * FROM employees;

Output:

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

The output indicates that the employees with department id 1 were deleted successfully.

Summary

• Use PostgreSQLDELETE CASCADE action to automatically delete related rows in child tables when a parent row is deleted.

Last updated onFebruary 23, 2024

On this page

• Introduction to the PostgreSQL DELETE CASCADE
• PostgreSQL DELETE CASCADE example
• Summary