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
– zambotn, Commented Feb 5, 2012 at 16:20
@user1089668, I undertand this is a typo because in error you can see that "vehicule_uid" references "vehicule_loan". — dani herrera
– dani herrera, Commented Feb 5, 2012 at 17:03

Erwin Brandstetter · Accepted Answer · 2012-02-05 17:19:48Z

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 5, 2012 at 17:19

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

answered Feb 5, 2012 at 16:19

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Erwin Brandstetter Over a year ago

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 Over a year ago

@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 Over a year ago

+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 Over a year ago

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

Erwin Brandstetter Over a year ago

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

|

Collectives™ on Stack Overflow

postgresql and Delete statement violates foreign key constraint

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related