In PostgreSQL, a DELETE CASCADE is a powerful feature that is useful when you have multiple tables linked with each other via foreign key constraints. When a DELETE CASCADE feature is enabled, deleting a record from the referenced/parent table will also delete the referencing records from the child table.

Try the new PgManage (Open Source) and get rid of PgAdmin!

This Postgres blog will present a step-by-step guide on how to use the DELETE CASCADE option in Postgres. So, let’s start!

When to Use DELETE CASCADE in Postgres?

For instance, consider a database with a "customer_details" and an "order_details" table. The "order_details" table is linked with the "customer_details" table through a foreign key constraint. Suppose a user wants to delete a customer from the "customer_details" table. Then to keep the database clean and organized, he might also want to delete all of the orders associated with that customer from the "order_details" table. This is where Postgres’ DELETE CASCADE feature is extremely useful!

How to Use DELETE CASCADE in Postgres?

To use a delete cascade in Postgres, specify the "ON DELETE CASCADE" option while creating/defining a foreign key constraint. This tells Postgres to automatically delete any rows in the referenced table that are related to the row being deleted in the referencing table.

Let’s comprehend it practically!

Contact us today for all your Postgres and Open Source consulting and support needs.

Example: How Does the DELETE CASCADE Work in Postgres?

This example will present the stepwise instructions to use the DELETE CASCADE option in Postgres:

Step 1: Create Sample Tables

Firstly, let’s create a “cutomer_details” table with two columns: “cust_id” and “cust_name”:

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

Now create one more table named “order_details” with two regular columns and 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
);

An “order_details” table with a foreign key named “customer_id” having the DELETE CASCADE feature enabled has been created.

Step 2: Insert Data Into Sample Tables

Let’s insert some records into the “customer_details” table via the “INSERT INTO” command:

INSERT INTO customer_details (cust_id, cust_name)
VALUES(1, 'Joe'),
(2, 'Mike'),
(3, 'Joseph');

The specified data has been inserted into the “customer_details” table successfully. Now, execute the INSERT INTO command one more time to insert the data into the “order_details” table:

INSERT INTO order_details (order_id, customer_id, order_date)
VALUES(1, 1, '2022-12-12'),
(2, 1, '2022-12-12'),
(3, 2, '2022-11-15'),
(4, 3, '2022-12-16'),
(5, 2, '2022-12-18');

Five records have been inserted into the order_details table successfully.

Step 3: Verify Tables’ Data

Execute the “SELECT *” query to check the data of the “customer_details” table:

Now execute the “SELECT *” command one more time to fetch the data of the “order_details” table:

The output snippet shows the data of both tables.

Step 4: DELETE CASCADE

Since we set/enabled the DELETE CASCADE feature while creating a foreign key, so deleting a record from the “customer_details” table will also delete the corresponding record from the “order_details” table:

DELETE FROM customer_details 
WHERE cust_id = 1;

The output shows that the DELETE command was executed successfully.

Step 5: Verify Record’s Deletion

To verify the working of the DELETE CASCADE feature, let’s execute the “SELECT *” command as follows:

The output snippet shows that the customer having id 1 has been deleted from the customer_details table. Now, let’s execute the “SELECT *” command one more time to see if the selected record has been deleted from the child table or not:

The output snippet proves that Postgres automatically deleted the targeted record from the child table. This is how the DELETE CASCADE feature works in Postgres.

Conclusion

In PostgreSQL, a DELETE CASCADE allows us to delete the records associated with some other tables (via foreign key constraints). To use a delete cascade in Postgres, specify the "ON DELETE CASCADE" option while creating/defining a foreign key constraint. By doing so, Postgres will automatically delete any rows in the referenced table that are related to the row being deleted in the referencing table. This Postgres blog has explained the usage of the DELETE CASCADE keyword via practical examples.