postgres key is not present in table constraint

Question

When trying to ALTER TABLE in Postgres 9.5 to create foreign key constraint: from product_template.product_brand_id to product_brand.id

ALTER TABLE public.product_template
    ADD CONSTRAINT product_template_product_brand_id_fkey 
    FOREIGN KEY (product_brand_id)
    REFERENCES public.product_brand (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE SET NULL;

Returns error

ERROR:  insert or update on table "product_template" violates foreign key         constraint "product_template_product_brand_id_fkey"
DETAIL:  Key (product_brand_id)=(12) is not present in table "product_brand".
STATEMENT:  ALTER TABLE "product_template" ADD FOREIGN KEY ("product_brand_id") REFERENCES "product_brand" ON DELETE set null

Im confused why postgres is trying to find product_brand.product_brand_id, when the fkey is from product_template.product_brand_id to product_brand.id

Any ideas?

score 20 · Accepted Answer · 2017-09-19 10:09:17Z

20

The error message simply states that there is at least one row in the table product_template that contains the value 12 in the column product_brand_id

But there is no corresponding row in the table product_brand where the column id contains the value 12

Key (product_brand_id)=(12) relates the source column of the foreign key, not the target column.

edited Sep 19, 2017 at 10:09

answered Sep 19, 2017 at 9:59

user330315

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

4 Comments

aagjalpankaj Over a year ago

Can't we allow to add the foreign key constraint though there are such values in the column which are not present in the foreign table?

user330315 Over a year ago

@aagjalpankaj: if you want to allow invalid data, then don't add the constraint

aagjalpankaj Over a year ago

I have to add FK constraint but wanted to ignore the existing values and the constraint should work for future values. Is there any way to achieve this?

user330315 Over a year ago

No, you either need to delete those rows completely or set the FK column to NULL

jsonbourne · Accepted Answer · 2019-09-19 06:58:39Z

0

In simple terms, the value of FOREIGN KEY(product_brand_id) provided in your ALTER statement is not present in the source (product_brand) table.

answered Sep 19, 2019 at 6:58

jsonbourne

9832 gold badges10 silver badges17 bronze badges

Collectives™ on Stack Overflow

postgres key is not present in table constraint

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related