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!
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:
1
CREATE TABLE table_name (
2
column_name data_type,
3
CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column_name)
4
);
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 an id column and the department table has an employee_id column. 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 populate the tables with this query:
1
INSERT INTO employees (name)
2
VALUES ('Alice'), ('Bob'), ('Carol'), ('David');
3
4
INSERT INTO departments (employee_id, department_name)
5
VALUES (1, 'Sales'), 2, ('Marketing'), (3, 'Accounting'), (1, 'Marketing');
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:
1
DELETE FROM employees WHERE id = 1;
This will delete two rows from the departments table with ids of 1 and 4, which both reference the employee id of 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:
Tips for Using ON DELETE CASCADE Safely and Effectively
Here are some tips for using ON DELETE CASCADE:
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.
