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 forpsql
, 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