intro
In this article, we are going to look at how to use the ON DELETE CASCADE feature in Postgres to delete child records automatically when you delete a parent record using a tool like DbVisualizer. Dig in!
Preface
You have 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.
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.
Understanding Postgres ON DELETE CASCADE
The ON DELETE CASCADE, a referential action in Postgres, allows database developers to automatically delete related rows in child tables when a parent row is deleted from the parent table. This feature makes sure that dependent rows are deleted along with their related rows, which helps you preserve referential integrity in the database.
To implement the DELETE CASCADE action, you need to have two related tables: the parent_table and the child_table:
1
CREATE TABLE parent_table(
2
id SERIAL PRIMARY KEY,
3
...
4
);
5
6
CREATE TABLE child_table(
7
id SERIAL PRIMARY KEY,
8
parent_id INT,
9
FOREIGN_KEY(parent_id)
10
REFERENCES parent_table(id)
11
ON DELETE CASCADE
12
);
Here, the first table, ie. the parent_table has a unique identifier, and the second table, the child_table contains a reference to that identifier. The foreign key relationship allows each record in the child_table to be linked to a specific record in the parent_table. The ON DELETE CASCADE clause ensures that if a parent record is deleted, all associated child records are automatically removed.
ON DELETE CASCADE Example
For an example illustrating how ON DELETE CASCADE works in Postgres, assume we have two tables namely employees and departments. The lecturers table has an id column and the facultiestable has an faculty_id column. A foreign key constraint can be created on the faculty_id column in the facultiestable to create a relationship between the two tables by referencing the id column in the lecturers table.
The query below will create the lecturers and faculties table and will also define the foreign key constraint:
It is worth noting in this instance, that a faculty may have one or more lecturers and each lecturer belongs to a faculty. In the lecturers table, the faculty_id is a foreign key that references the id column of the faculties table.
The foreign key has the ON DELETE CASCADE clause that specifies the referential action to take when a row in the faculties table is deleted.
Next, populate the tables with this query:
1
INSERT INTO faculties (name)
2
VALUES
3
('Science'),
4
('Humanities')
5
6
INSERT INTO lecturers (name, faculty_id)
7
VALUES
8
('Okai Wenger', 1),
9
('Bassuah Kelvin', 1),
10
('Mike Smith', 2);
Running the query will result in the two tables below:
Now to experience the effect of the cascade action firsthand, delete a faculty and observe the effect on the associated lecturers:
1
DELETE FROM departments
2
WHERE id = 1;
Once this statement is executed, the row from the faculties table with the id of 1, will be deleted. It will then subsequently delete all lecturers belonging to the faculty with faculty_id = 1 due to the DELETE CASCADE action defined on the foreign key constraint.
Without the ON DELETE CASCADE option, you would need to manually delete these rows from the table which can be time-consuming and fallible.
Finally, this is how the lecturers table will look like in DbVisualizer after the query has been executed:
Great! We have been able to implement and test the ON DELETE CASCADE feature and with the help of DbVisualizer, observed what goes on within the tables when the feature is implemented.
Time to 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:
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, Postgres 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, Postgres 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. Through the use 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 is not 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.
