While working with the databases like PostgreSQL, users use basic operations like create, read, update, and delete. The delete operation becomes a bit tricky if the data to be deleted is linked to other tables. To perform this operation without any hassles, the DELETE CASCADE feature is used. This feature automatically removes child records when their parent is deleted.
Quick Outline
This post will explain the working of the Postgres DELETE CASCADE option using the following content:
- What is DELETE CASCADE in PostgreSQL?
- What is the Need for DELETE CASCADE in PostgreSQL?
- How Does DELETE CASCADE Work in PostgreSQL?
What is DELETE CASCADE in PostgreSQL?
The “DELETE CASCADE” is a useful Postgres feature that ensures referential integrity by deleting all the referencing records in child/referenced tables when a corresponding record is deleted from the parent/referencing table.
What is the Need for DELETE CASCADE in PostgreSQL?
The need for the “DELETE CASCADE” feature arises when we try to delete a record that is dependent (via a foreign key) on any other record. In such cases, the simple DELETE statement doesn’t work, here is a practical demonstration that will help you understand this concept better.
Example: Understanding the Need for DELETE CASCADE
Suppose we have an existing table named “test_marks” whose details can be fetched by executing the following meta-command:
\d test_marks;
The table description shows that the “test_marks” (parent) table is referenced by the “std_grades” (child) table:
Now, let’s assume, for some reason we want to delete a record from the “test_marks” table whose “test_id” is “2”. For this purpose, we will execute the DELETE query as follows:
DELETE FROM test_marks
WHERE test_id = 2;
An error has arisen that says the “update or delete” operation on the selected table violates the foreign key constraint:
To overcome this problem the DELETE CASCADE feature is used in Postgres.
How Does DELETE CASCADE Work in PostgreSQL?
The DELETE CASCADE feature allows us to delete dependent records/objects from the database. To use this feature, we need to enable the “ON DELETE CASCADE” while defining a foreign key constraint in the table. Doing so will allow Postgres to automatically delete any record in the referenced(child) table that is associated with the record being deleted in the referencing(parent) table.
The below-stated steps will explain the working of the DELETE CASCADE option from scratch.
Step 1: Create a Referencing Table
Let’s create a parent/referencing table, “student_details” in our case:
CREATE TABLE student_details (
student_id INTEGER PRIMARY KEY,
student_name TEXT NOT NULL
);
The given screenshot shows that the “student_details” table with “student_id” and “student_name” columns has been created successfully:
Step 2: Create a Referenced Table
Now, create a child/referenced table that contains a foreign key constraint:
CREATE TABLE test_details(
test_id INTEGER PRIMARY KEY,
student_id INTEGER REFERENCES student_details (student_id) ON DELETE CASCADE,
obtained_marks INT
);
This will create a new “test_details” table with the “ON DELETE CASCADE” option enabled:
Step 3: Insert Data Into Tables
Insert the data of your choice into both newly created tables:
INSERT INTO student_details(student_id, student_name)
VALUES(1, 'John'),
(2, 'Miller'),
(3, 'Alex')
RETURNING *;
Here, the INSERT query is used to insert three rows into the student_details table and a “RETURNING *” clause is used to fetch the newly inserted data:
Execute the “INSERT” query one more time to insert the data into the “test_details” table as well:
INSERT INTO test_details(test_id, student_id, obtained_marks)
VALUES(1, 1, 74),
(2, 2, 60),
(3, 3, 40)
RETURNING *;
Step 4: Delete a Record From the Parent/Referencing Table
Let’s execute the “DELETE” query on the parent table and see how it works:
DELETE FROM student_details
WHERE student_id = 2;
The student having “student_id = 2” is successfully deleted from the “student_details” table:
Step 5: Verify Record Deletion
Now execute the SELECT query with respective table names to see the changes made in both these tables:
SELECT * FROM student_details;
SELECT * FROM test_details;
The record that we deleted from the parent table also vanishes from the child table.
This is how the DELETE CASCADE option works in Postgres.
Final Thoughts
The “DELETE CASCADE” feature in PostgreSQL ensures referential integrity by automatically deleting all child records when the corresponding parent record is deleted. To enable this feature, the “ON DELETE CASCADE” clause must be used while defining a foreign key constraint. This post has comprehensively explained what DELETE CASCADE is, when to use it, and how to use it in PostgreSQL. All these concepts are discussed with suitable examples.
