Cascade Delete

Working Sample

Consider a scenario in which there is a database containing tables named "customer_details" and "order_details". The "order_details" table is connected to the "customer_details" table by means of a foreign key restriction. In the event that a user wishes to remove a customer entry from the "customer_details" table, they may also want to eliminate all corresponding orders linked to that customer from the "order_details" table in order to maintain the organization and cleanliness of the database. This is where the DELETE CASCADE functionality in Postgres proves to be highly advantageous.

To begin with, we will establish a table named "customer_details" consisting of two columns: "cust_id" and "cust_name."

CREATE TABLE customer_details (
cust_id INTEGER PRIMARY KEY,
cust_name TEXT NOT NULL
);

Generate an additional table titled "order_details" containing two standard columns alongside a foreign key.

CREATE TABLE order_details(
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customer_details (cust_id) ON DELETE CASCADE,
order_date DATE
);

A new table called "order_details" has been constructed, which includes a foreign key named "customer_id" that has been configured to utilize the DELETE CASCADE functionality.

Let us insert records into the tables.

The "order_details" will hold this data now.

Due to the activation of the DELETE CASCADE setting during the foreign key creation process, the removal of a record from the "customer_details" table will consequently result in the deletion of the associated record from the "order_details" table as well.

DELETE FROM customer_details 
WHERE cust_id = 1;

This deleted the customer from "customer_details" table and also removed the corresponding orders from the "order_details" table.

Types

There are five possible choices available for the deletion of foreign key constraints.

• CASCADE: The deletion of a row in the main table results in the deletion of all associated rows in the dependent tables.

• RESTRICT: Upon deletion of a row from the parent table, the delete process is prevented from proceeding if there exist any associated rows in the child tables.

• SET NULL: When a record is removed from the primary table, the foreign key columns in the related tables are updated to reflect NULL values.

• SET DEFAULT: When a row is removed from the primary table, the foreign key columns in the subordinate tables are updated to default values.

• NO ACTION: This feature is comparable to the RESTRICT option, but it includes the capability to be "deferred" until the conclusion of a transaction. It allows other cascading deletes to be executed first, with the delete constraint only triggering an error if there is referenced data remaining at the conclusion of the transaction.