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 ?

in the foreign key declaration you have `vehicule(vehicule)` but there's no vehicule table! — zambotn, Feb 05 '12 at 16:20
@user1089668, I undertand this is a typo because in error you can see that "vehicule_uid" references "vehicule_loan". — dani herrera, Feb 05 '12 at 17:03

score 6 · Accepted Answer · edited Feb 05 '12 at 17:19

6

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.

edited Feb 05 '12 at 17:19

Erwin Brandstetter

605,456
145
1,078
1,228

answered Feb 05 '12 at 16:19

dani herrera

48,760
8
117
177

You are copying the error from the question. The fk constraint references a table `vehicule` while you talk about a table `vehicule_loan`. – Erwin Brandstetter Feb 05 '12 at 16:35
1

@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? – dani herrera Feb 05 '12 at 16:46
+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. – Erwin Brandstetter Feb 05 '12 at 17:09
@ErwinBrandstetter, thanks about answer improvements, you have rewrote answer. You look so good for 92 (your age in your profile). – dani herrera Feb 05 '12 at 17:21
Daily training with PostgreSQL keeps a man's skin smooth. I am sure you knew that? :) – Erwin Brandstetter Feb 05 '12 at 17:23
May be in Austria ;) Nice to meet you @ErwinBrandstetter. – dani herrera Feb 05 '12 at 17:25

postgresql and Delete statement violates foreign key constraint

1 Answers1