POSTGRESQL

Postgres ON DELETE CASCADE - A Guide

intro

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
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

Preface

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.

ON DELETE CASCADE Explained

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:

Copy
        
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:

Copy
        
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.

Summary

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.

FAQs

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
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

A Complete Guide to pg_dump With Examples, Tips, and Tricks

author Antonello Zanini tags pg_dump POSTGRESQL 8 min 2024-01-25
title

A Comprehensive Guide to Data Types in Postgres

author Leslie S. Gyamfi tags DATA TYPES POSTGRESQL 10 min 2024-01-23
title

SQL DISTINCT: A Comprehensive Guide

author Bonnie tags DISTINCT POSTGRESQL SQL 5 MINS 2024-01-11
title

Date Formatting in Postgres: A Comprehensive Guide

author Leslie S. Gyamfi tags DATE POSTGRESQL 7 MINS 2023-12-11
title

Casting in PostgreSQL: Handling Data Type Conversions Effectively

author Leslie S. Gyamfi tags CAST POSTGRESQL 5 MINS 2023-12-07
title

PostgreSQL Materialized Views: A Beginner's Guide

author Ochuko Onojakpor tags POSTGRESQL VIEWS 7 MINS 2023-12-04
title

Postgres List Users: Two Different Approaches 

author Antonello Zanini tags LIST USERS POSTGRESQL 3 MINS 2023-11-16

The content provided on dbvis.com/thetable, 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 ↗