Postgres ON DELETE CASCADE - A Guide


This article explains how to use the ON DELETE CASCADE feature in PostgreSQL to delete child records automatically when you delete a parent record. Find out everything about it in this blog!

Tools used in the tutorial
Tool Description Link


You’ve probably heard about ON DELETE CASCADE in Postgres. In relational databases, a foreign key constraint is a constraint that links a column in one table to a column in another table. The foreign key column must contain a value also present in the primary key column of the referenced table.

ON DELETE CASCADE is used to specify that when a row is deleted from the parent table, all rows in the child table that reference the deleted row should also be deleted. This is useful for maintaining the integrity of the database.

Understanding Foreign Key Constraints

A foreign key constraint is a constraint that ensures referential integrity between tables in a relational database. It establishes a relationship between a column or a set of columns in one table and a column(s) in another. The column(s) in the first table is called the foreign key, while the column or set of columns in the second table is called the referenced key. In Postgres, the syntax for creating a foreign key constraint is as follows:

SQL Code in DbVisualizer.
SQL Code in DbVisualizer.

The Role of Foreign Key Constraints

Foreign key constraints are integral parts of database designs that are responsible for maintaining the consistency of data in relational databases. They do so by preventing the insertion of inconsistent data in databases. Now that we have a fair understanding of what foreign key constraints are, let us try to understand what the ON DELETE CASCADE feature/option in Postgres is.

What is the ON DELETE CASCADE Option?

The ON DELETE CASCADE query is an option that can be used when defining a foreign key constraint. When ON DELETE CASCADE is specified, Postgres automatically deletes any row in the child table that references a deleted row from the parent table.

DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.


For an example illustrating how ON DELETE CASCADE works in PostgreSQL, let’s assume we have two tables namely employees and departments. The employees table has a column called id and the department table has a column called employee_id. A foreign key constraint can be created on the employee_id column in the department table to create a relationship between the two tables by referencing the id column in the employees table.

The SQL query below will create the employees and departments and will also define the foreign key constraint:

SQL Code for Tables and Foreign Key Constraint.
SQL Code for Tables and Foreign Key Constraint.

Next, let us build a query that will input data into the created tables as shown below:

1 INSERT INTO employees (name) 2 VALUES ('Alice'), ('Bob'), ('Carol'), ('David'); 3 INSERT INTO departments (employee_id, department_name) 4 VALUES (1, 'Sales'), (2, 'Marketing'), (3, 'Accounting'), (1, 'Marketing');

This is how the code will appear in the SQL Commander of DbVisualizer:

SQL Code in SQL Commander of DbVisualizer.
SQL Code in SQL Commander of DbVisualizer.

Running the query will result in the two tables below:

Employees and Department Tables.
‘Employees’ and ‘Department’ Tables.

Here, the departments table has a foreign key constraint on the employee_id column that references the id column in the employees table. The ON DELETE CASCADE option is specified, which means that if a row in the employees table is deleted, all rows in the departments table that reference that row will also be deleted automatically.

If you delete the row from the employees table with id of 1, all rows in the departments table that reference the employee id of 1 will also be deleted automatically:

Running the DELETE Query in DbVisualizer.
Running the DELETE Query in DbVisualizer.

This will delete two rows from the departments table with IDs 1 and 4, which both reference the employee ID 1. Without the ON DELETE CASCADE option, you would need to manually delete these rows from the departments table which can be time-consuming and fallible. This is how the departments table will look like in DbVisualizer after the query has been executed:

The New Departments Table.
The New ‘Departments’ Table.

Great! We’ve been able to implement and test the ON DELETE CASCADE feature and with the help of DbVisualizer, we’ve observed what goes on within the tables when the feature is implemented. Let’s bore down to studying some of the corner cases in using the ON DELETE CASCADE feature in Postgres.

Does ON DELETE CASCADE Always Help? Corner Cases

Many developers, generally, believe that ON DELETE CASCADE is a useful tool for maintaining database integrity. This is absolutely true, however, there are some corner cases developers should be aware of:

  1. Database Performance: Cascade deletes in Postgres can be slow, especially if there are a lot of rows in the child table or when dealing with large tables. You may want to use a different constraint, such as SET NULL or SET DEFAULT if you happen to have issues with your database performance.
  2. Data Loss: Cascading deletes can be dangerous if not used carefully. For instance, if a row is accidentally deleted from a referenced table, all referencing rows in the referencing table will also be deleted, potentially leading to data loss. This can lead to data loss if you're not careful.

Tips for Using ON DELETE CASCADE Safely and Effectively

Here are some tips for using ON DELETE CASCADE:

  1. Only use it on tables where you're confident that deleting a row will not lead to loss of data. Before implementing ON DELETE CASCADE, test to ensure that it works as expected. This can help identify any unexpected behavior.
  2. Cascading deletes can be resource-intensive, so it's best to use it with smaller tables. Consider using triggers or stored procedures for larger tables with complex relationships as large tables with complex relationships.
  3. Use it in conjunction with other constraints, such as UNIQUE and CHECK, to help prevent inconsistent data. For instance, you can use the UNIQUE constraint to ensure that no two rows in a table have the same value for a particular column. You can also use the CHECK constraint to enforce specific data conditions, such as demanding that a column contains a value between x and y.

Let’s look at an example using the query below:

1 CREATE TABLE students ( 2 id serial PRIMARY KEY, 3 name text NOT NULL, 4 height float NOT NULL CHECK (height BETWEEN 1 AND 2.7), 5 UNIQUE (name, height) 6 );

In the query above, the students table has two constraints: UNIQUE and CHECK. If you try to insert a new row with the same name and height as an existing row, PostgreSQL will not allow you to do so. This is because the UNIQUE constraint prevents duplicate rows.

Similarly, if you try to insert a new row with a height outside of the range of 1 to 2.7, PostgreSQL will not allow you to do so. This is because the CHECK constraint prevents invalid data.


ON DELETE CASCADE is a powerful feature in Postgres that can make database management easier and ensure referential integrity. By employing this feature, you can define that a foreign key constraint automatically deletes all referencing rows in child tables when a corresponding row is deleted from the parent table.

ON DELETE CASCADE isn’t everything, to take proper care of your databases, consider using SQL clients like the one built by DbVisualizer.


What happens if a foreign key constraint with ON DELETE CASCADE is applied to a table that already contains data?

If a foreign key with ON DELETE CASCADE is applied to an existing table with data, any row in the referencing table that points to a deleted row in the referenced row will also be automatically deleted.

Can ON DELETE CASCADE be used to delete rows from multiple tables at once?

No, ON DELETE CASCADE applies only to the referencing table, and any cascading deletes are limited to that table alone. If you need to delete rows from multiple tables at once, you can use a trigger or a stored procedure to perform the necessary actions.

Is it possible to specify multiple actions to be taken when a row is deleted using ON DELETE CASCADE?

No, it is not possible to specify multiple actions to be taken when a row is deleted using ON DELETE CASCADE. Instead, you can create multiple foreign key constraints with different actions, and apply them to the same referencing table if you need to perform multiple actions.

Dbvis download link img
About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09

The content provided on, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗