postgres key is not present in table constraint
19,772
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.
Related videos on Youtube
![Adam Sims](https://i.stack.imgur.com/Loq34.jpg?s=256&g=1)
Author by
Adam Sims
Updated on June 04, 2022Comments
-
Adam Sims about 2 years
When trying to ALTER TABLE in Postgres 9.5 to create foreign key constraint: from
product_template.product_brand_id
toproduct_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 fromproduct_template.product_brand_id
toproduct_brand.id
Any ideas?
-
aagjalpankaj almost 5 yearsCan'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?
-
a_horse_with_no_name almost 5 years@aagjalpankaj: if you want to allow invalid data, then don't add the constraint
-
aagjalpankaj almost 5 yearsI 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?
-
a_horse_with_no_name almost 5 yearsNo, you either need to delete those rows completely or set the FK column to NULL