postgresql and Delete statement violates 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 a vehicule from the table vehicule_loan I want that referencing rows in the table vehicule_uid are 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 a vehicule from the table vehicule_loan, the vehicule in vehicule_uid would point to nothing.

But is there a way to keep the rows in vehicule_uid ?

Solution

You should allow NULL values in the foreign key attribute and define the foreign key constraint as ON DELETE SET NULL.

I quote chapter 5.3. Constraints from the PostgreSQL manual:

There are two other options: SET NULL and SET DEFAULT. These cause the referencing columns to be set to nulls or default values, respectively, when the referenced row is deleted.

Could 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 setting, when you delete a row in vehicule_loan all referencing rows in vehicule_uid remain in database.

Comments

[Erwin Brandstetter]

You are copying the error from the question. The fk constraint references a table vehicule while you talk about a table vehicule_loan.

[dani herrera]

@ErwinBrandstetter, thaks about your appointment. Fixed now in my answer. OP and I have change vehicule_loan to vehicle, but, as you can see, this is not the major issue, only a typo (typographical error). Raise error also talks about vehicule_uid table, that means that foreign key in real database is well writed. You agree?

[Erwin Brandstetter]

+1 I agree and I think your answer is just what the OP needs. I took the liberty to apply minor improvements and also fixed the question.

[dani herrera]

@ErwinBrandstetter, thanks about answer improvements, you have rewrote answer. You look so good for 92 (your age in your profile).

[Erwin Brandstetter]

Daily training with PostgreSQL keeps a man's skin smooth. I am sure you knew that? :)

[dani herrera]

May be in Austria ;) Nice to meet you @ErwinBrandstetter.