PostgreSQL Cascade Delete
“When working with PostgreSQL databases, you will encounter instances where you must drop records from a table. Before running a delete query, it is important to ensure that the table does not leave any incomplete relationships between tables.
In PostgreSQL, we can use the CASCADE DELETE feature to accomplish this. This feature allows a delete operation to remove the specified records and any foreign keys that reference them.
Using this article, we will review how to perform a cascade delete in PostgreSQL.”
PostgreSQL Cascade Delete
The Cascade delete feature is very simple. It ensures that deleting any parent records removes the child records as well. To avoid accidentally removing child records, you can set the foreign key constraints in the parent table to NULL.
Let us see how we can accomplish this.
Start by setting up a sample database. In your psql utility, run the command:
|
1
2 3 4 5 6 7 8 |
CREATE DATABASE "cascade_delete_db;"
WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = pg_default CONNECTION LIMIT = -1; |
Once created, switch to that database by running the command:
|
1
|
\c cascade_delete_db;
|
Next, let’s create the parent table by running the query:
|
1
2 3 4 5 6 7 |
CREATE TABLE users(
id SERIAL PRIMARY KEY, full_name VARCHAR(100), email VARCHAR(255), country VARCHAR(50), department_foreign_key INT NOT NULL ); |
Next, we can create the child table as:
|
1
2 3 4 |
CREATE TABLE department(
user_id SERIAL PRIMARY KEY, department VARCHAR(50) ); |
Next, we need to add a foreign key constraint to the parent table, as shown in the command below:
|
1
2 |
ALTER TABLE users ADD FOREIGN KEY(department_foreign_key)
REFERENCES department(user_id) ON DELETE CASCADE; |
We can now insert some sample data into the tables as shown:
|
1
2 3 4 5 6 7 8 |
INSERT INTO department(department) VALUES
('GAME DEVELOPMENT'), ('DEV-OPS'), ('BACKEND-DEV'); INSERT INTO users(full_name, email, country, department_foreign_key) VALUES ('Alley K', 'k.alley@test.gg', 'US', 1), ('Kaspa V', 'v.ghost@kaspa.ww', 'CA', 2), ('Dev.W', 'w@dev.io', 'AF', 3); |
We can then query the tables as:
|
1
|
SELECT * FROM users;
|
This should return the table:
From the department’s table, we can run:
|
1
|
SELECT * FROM department;
|
The resulting table:
Once all the sample data is ready, we can perform a cascade delete as shown in the query below:
|
1
|
DELETE FROM department WHERE department='DEV-OPS';
|
In the statement above, we use the normal delete query to remove a record where the department is equal to “DEV-OPS.” Since we set the cascade delete, we can check the users’ table to verify the user with the department_foreign_key of 2 is dropped.
|
1
|
SELECT * FROM users;
|
We can see that only two records are in the users’ table despite us removing the record from the departments’ table.
Congratulations, you have learned how to perform a cascade delete in PostgreSQL.
Termination
This tutorial taught you how to link two tables using a foreign key and set a cascade delete feature.
Thanks for reading!!
Source: linuxhint.com