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!
DbVisualizer, top rated database management tool and SQL client.
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:
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.
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:
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.
Running the query will result in the two tables below:
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:
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:
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:
- 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.
- 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:
- 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. - 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.
- Use it in conjunction with other constraints, such as
UNIQUEandCHECK, to help prevent inconsistent data. For instance, you can use theUNIQUEconstraint to ensure that no two rows in a table have the same value for a particular column. You can also use theCHECKconstraint 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.
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.
About the author
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
Top comments (0)