JIYIK CN >

ON DELETE CASCADEBefore we start looking at different solutions for implementing in PostgreSQL , we will first understand ON DELETE CASCADEwhat is and what it does.

Let's assume you have two tables, one of which inherits from a parent table KEYand uses its values, also called ASSOCIATION. Now suppose the user wants to delete this row in the parent table.

But wait? What happens to the inherited rows in the child table?

You might think that this VIOLATIONwould raise an error. Correct! Fortunately, this does not work and will produce an error.

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

But what if you don't want to restrict DELETE, but continue to get this row from both tables . This is where we come in.DELETE

Let’s see how it works.

Using in PostgreSQLON DELETE CASCADE

Let's start by creating a Vehicletable.

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

Now let's define another BUStable called that will VEHICLEinherit its keys from ID.

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

You can IDsee REFERENCESthe tag at the end of the definition. This means that it now refers to VEHICLEa row in the table, and any operations on that table that do not match IDthe one VEHICLEin the table will be rejected.IDINSERT

Now let's assume that we have some values VEHICLE​​in the table .INSERT

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

Let's also add INSERTa value to BUSthe table.

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

So now BUStable refers VEHICLEto the table KEY``2, which means High_Vanbelongs to John.

Now let's try BUSto remove this entry from .

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 BUSthat is still referenced in the table KEY``2. Therefore DELETEwill not work. Now let's define DELETEwhat happens if we call .

In BUSthe table, modify IDthe column.

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

Now, when we try DELETE, it works perfectly. Why? Because CASCADEtends to delete the rows suggested for in the child table DELETE.

Let's say you'd prefer to use the original method and want to define yours. In that case, you could try ON DELETE CASCADEchanging to ON DELETE RESTRICT, which will ultimately restrict any conflicting DELETEoperations.

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

ON CASCADE DELETEA brief description of the problems faced in defining multiple constraints in PostgreSQL

Doing this to all inherited tables ON DELETE CASCADEcreates a problem when you delete rows that reference thousands of tables. This will create a problem, but rolling back any changes is extremely unlikely.

Always make sure to DELETEuse good practices for . If you want CASCADE, call DELETEthe function before and TRANSACTIONkeep checking if an error occurred instead of at the end.

This will keep your database safe and secure and avoid future problems.

We hope you have learned how to perform operations in PostgreSQL ON DELETE CASCADE.