PostgreSQL

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', '[email protected]', 'US', 1),
('Kaspa V', '[email protected]', 'CA', 2),
('Dev.W', '[email protected]', '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!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list