How to use the PostgreSQL DELETE CASCADE

Introduction

When you’re managing data in PostgreSQL, there will be times when you need to delete records. Before you perform a DELETE operation, it’s important to consider any foreign key relationships between the records to be deleted and records in other tables. In PostgreSQL, you can use the DELETE CASCADE statement to make sure that all foreign-key references to a record are deleted when that record is deleted. In this article, we’ll discuss the PostgreSQL DELETE CASCADE and review some examples of its use.

Prerequisites

Let’s review some key prerequisites that are necessary for this tutorial:

• You’ll need to have PostgreSQL installed and configured on your machine. Use the command psql -V to display the version number for psql, the interactive terminal used with PostgreSQL.

• It’s helpful to have some basic knowledge of database management systems and SQL commands.

PostgreSQL DELETE CASCADE

In PostgreSQL, a cascade means that a delete or update of records in a parent table will automatically delete or update matching records in a child table where a foreign key relationship is in place. If you want to avoid deleting records from the child table, you need to set the foreign key value for the parent table record to NULL.

PostgreSQL DELETE CASCADE foreign key

The table containing the foreign key is called the referencing table or child table. The parent table refers to the table to which the foreign key is related. A foreign key in the PostgreSQL child table is a reference to the primary key in the parent table.

Create database in PostgreSQL

Let’s create a new database with a unique name to use in our examples. The syntax to create a database is shown below:

NOTE : In this example, we chose to name our database test_db.

Creating a foreign key with the CASCADE DELETE option can be done though the CREATE TABLE and ALTER TABLE statements.

Shown below is the statement we’ll use to create a sample table:

In this example, the parent table is the employee table. It has a primary key, which is the emp_id field.

Next, let’s create a second table:

This process table will serve as the child table in our foreign key DELETE CASCADE example.

Alter data in a table

How will we establish this parent-child relationship between the tables and indicate that we want the DELETE CASCADE option to apply here? We can specify the DELETE CASCADE clause using the ALTER TABLE statement:

In the statement above, we specified ON DELETE CASCADE— this means that if the parent table is deleted, the child table will also be deleted.

Insert data in PostgreSQL table

Now that we created our PostgreSQL tables, we can insert some data into them.

The INSERT statement is used to insert rows in a table. It can be used to insert a single record or multiple records into a table into PostgreSQL.

Here’s an example:

We can also use an INSERT statement to add records to the parent table:

Let’s make sure our INSERT operations were successful using SELECT statements.

We can see the results for the employee table below:

The result for the process table should look like the following:

PostgreSQL in cascade delete into table

In this section, we’ll demonstrate how the DELETE CASCADE works in PostgreSQL.

The DELETE statement typically uses a WHERE clause to select rows from the specified table. In the absence of a WHERE clause, all rows in the table would be deleted.

We can see the DELETE CASCADE in action with the following statement:

Here, the cascade delete takes effect when we delete the record from the process table where the value of section is ‘distribution’. The cascade ensures that related records from the employee table will also be removed. Since the emp_id for the record in question is ‘1’ , all employees under process_fk 1 will be deleted.

Let’s verify that this worked correctly:

Why did these records get deleted? It happened because the distribution section was removed. Therefore, all employee assigned to that section would be dismissed from their job, so they were removed from the table.

Conclusion

When you delete records in PostgreSQL, it’s important to be mindful of foreign key relationships that may exist between these records and records in a different table. Using the DELETE CASCADE option can help ensure that all child records are also deleted when a parent record is deleted. In this article, we provided an overview of the PostgreSQL DELETE CASCADE option and provided examples of its use. If you’ve been following along with our instructions and examples, you’ll be prepared to utilize DELETE CASCADE in your own PostgreSQL tables.