How to use the PostgreSQL DELETE CASCADE

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
CREATE DATABASE test_db;

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:

1
2
3
4
5
CREATE TABLE employee(emp_id SERIAL PRIMARY  KEY,
name VARCHAR(30),
STATUS text,
phone_num VARCHAR(12),
process_fk INT NOT NULL);

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:

1
CREATE TABLE process(emp_id SERIAL PRIMARY KEY, SECTION VARCHAR(20));

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:

1
2
ALTER TABLE employee ADD FOREIGN KEY (process_fk)
REFERENCES process(emp_id) ON DELETE CASCADE;

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:

1
2
3
INSERT INTO process(SECTION) VALUES ('distribution');
INSERT INTO process(SECTION) VALUES ('curing');
INSERT INTO Process(SECTION) VALUES ('technology');

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

1
2
3
4
5
6
7
8
INSERT INTO employee(name,STATUS,phone_num,process_fk)
VALUES('joemarie','regular','0985959905','1'),
('shakhira','probationary','093948889487','2'),
('hyle','regular','095599093490','1'),
('kobe','probationary','097867556451','3'),
('nasty','regular','094458909099','2'),
('arianne','regular','097746890988','2');
INSERT 0 6

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

We can see the results for the employee table below:

1
2
3
4
5
6
7
8
9
10
testdb=# SELECT * FROM employee;
 emp_id |   name   |    STATUS    |  phone_num   | process_fk
--------+----------+--------------+--------------+------------
      1 | joemarie | regular      | 0985959905   |          1
      2 | shakhira | probationary | 093948889487 |          2
      3 | hyle     | regular      | 095599093490 |          1
      4 | kobe     | probationary | 097867556451 |          3
      5 | nasty    | regular      | 094458909099 |          2
      6 | arianne  | regular      | 097746890988 |          2
(6 ROWS)

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

1
2
3
4
5
6
7
testdb=# SELECT * FROM process;
 emp_id |   SECTION    
--------+--------------
      1 | distribution
      2 | curing
      3 | technology
(3 ROWS)

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:

1
DELETE FROM process WHERE SECTION='distribution';

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:

1
2
3
4
5
6
7
8
SELECT * FROM employee;
 emp_id |   name   |    STATUS    |  phone_num   | process_fk
--------+----------+--------------+--------------+------------
      2 | shakhira | probationary | 093948889487 |          2
      4 | kobe     | probationary | 097867556451 |          3
      5 | nasty    | regular      | 094458909099 |          2
      6 | arianne  | regular      | 097746890988 |          2
(4 ROWS)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.