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?

Chilopod

Answer 1

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.

Sharma

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? – Cathode

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

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? – Cathode

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

Answer 2

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.

Midmost