Postgresql and Delete violate foreign key constraint

Question

I have a problem with my delete statement.

I have two tables:

table vehicule_loan( vehicule TEXT NOT NULL UNIQUE, ); table vehicule_uid ( id UUID NOT NULL DEFAULT uuid_generate_v4(), vehicule TEXT NOT NULL REFERENCES vehicule_loan(vehicule) ON DELETE NO ACTION ); 

When I delete the vehicule from the vehicule_loan table, I want the row links in the vehicule_uid table to vehicule_uid kept.

But when I try to delete one, I get this error:

 ERROR: update or delete on table "vehicule_loan" violates foreign key constraint "vehicule_uid_vehicule_fkey" on table "vehicule_uid" 

I think I understand the error: After I delete the vehicule from the vehicule_loan table, the vehicule in vehicule_uid will not show anything.

But is there a way to save the lines in vehicule_uid ?

Answer 1

You must enable NULL values ​​in the foreign key attribute and define the foreign key constraint as ON DELETE SET NULL .

I quote chapter 5.3. Limitations from the PostgreSQL manual :

There are two more options: SET NULL and SET DEFAULT. This causes column references to be set to NULL or to default values, respectively, when the deleted row is deleted.

It might look like this:

 table vehicule_uid ( id uuid NOT NULL DEFAULT uuid_generate_v4(), vehicule text REFERENCES vehicule_loan(vehicule) ON DELETE SET NULL ); 

With this option, when you delete a row in vehicule_loan , all the reference lines in vehicule_uid remain in the database.