Foreign Key ON DELETE CASCADE in PostgreSQL

Before we begin looking at the different solutions to implement ON DELETE CASCADE in PostgreSQL, we will first understand what ON DELETE CASCADE means and what it does.

Let us suppose you have two tables, with one of them inheriting a KEY from the parent table and using its values, also known as ASSOCIATION. Now let’s say that a user wants to delete this row in the parent table.

But wait? What will happen to the inherited row in the child table?

You might be thinking there is an error that will be raised for this VIOLATION. Correct! Fortunately, this won’t work, and an error will be produced.

ERROR:  update or delete on table [your_table] violates foreign key constraint [f_key] on table [your_table]

But what if you didn’t want to restrict the DELETE, but rather go ahead and DELETE this row from both tables. That is where our operation comes in.

Let us see how it works.

Use ON DELETE CASCADE in PostgreSQL

Let’s begin with creating a Vehicle table first.

create table vehicle (
	id int PRIMARY KEY,
	OWNER TEXT
);

Now let’s define another table called BUS, which will inherit the key ID from VEHICLE.

create table bus (
	id int PRIMARY KEY references vehicle,
	Model TEXT
);

You can see the REFERENCES tag at the end of the definition of ID. This means that it now references the rows from the VEHICLE table, and any INSERT operation in this table where the ID does not match the ID in the VEHICLE table will be rejected.

Now let’s suppose that we INSERT some values into the VEHICLE table.

Insert into vehicle values (1, 'mark'), (2, 'john');

And let’s also INSERT a value into the BUS table.

insert into bus values (2, 'High_Van');

So now the BUS table refers to the KEY 2 in the VEHICLE table, meaning that High_Van belongs to John.

Now let’s try to delete this entry from BUS.

If you call:

delete from vehicle where id = 2

An error will be returned.

ERROR:  update or delete on table "vehicle" violates foreign key constraint "bus_id_fkey" on table "bus"
DETAIL:  Key (id)=(2) is still referenced from table "bus".

This tells you that KEY 2 is still referenced in the table BUS. Thus the DELETE would not work. Now let’s define what should happen if we call DELETE.

In the BUS table, modify the column ID.

create table bus (
	id int PRIMARY KEY references vehicle ON DELETE CASCADE,
	Model TEXT
);

Now when we try to DELETE, it works perfectly. Why? Because CASCADE tends to drop the row proposed for DELETE in the child table.

Suppose you are better off with the original and want to define your method. In that case, you can try changing the ON DELETE CASCADE to ON DELETE RESTRICT, which will eventually restrict any DELETE operations that come into conflict.

You can even use these options for the ON UPDATE operation in other ways.

a Short Note on the Issues Faced on Defining Multiple ON CASCADE DELETE Constraints in PostgreSQL

An ON DELETE CASCADE put on all the inheriting tables will make an issue when you delete a row that references thousands of tables. This will create an issue but rolling back to any changes will be highly unlikely.

Always make sure to use good practices for DELETE. If you want to CASCADE, call a function for DELETE, then make a TRANSACTION and constantly check for mishappenings rather than at the end.

This will guarantee the security and safety of your database and avoid problems in the future.

We hope you learned how to do the ON DELETE CASCADE operation in PostgreSQL.