PostgreSQL: Insert violates foreign key constraint

Question

I'm working on a project for which I want to use inheritance (see code below)

When I try to insert something into Profile there is an error.

ERROR:  insert or update on table "profile" violates foreign key 
constraint "profile_id_fkey"
DETAIL:  Key (id)=(21) is not present in table "test".

I'm using PSequel to inspect the database and the values are visible in the parent. However, I am still able to insert a duplicate primary key in the parent. The insert then works.

CREATE TABLE Test ( 
ID INTEGER NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE Testchild (
PRIMARY KEY (ID) 
) INHERITS (Test)
;


CREATE TABLE Profile (
ProfileID INTEGER NOT NULL, 
ID INT NOT NULL, 
PRIMARY KEY (ProfileID)
);

ALTER TABLE Profile
ADD FOREIGN KEY (ID) REFERENCES Test(ID);

INSERT INTO Testchild VALUES (21);

INSERT INTO Profile VALUES (1,21);

Your FK relationship is with Test table, not with TestChild. Try swapping in the Test table in the first insert and it will work — Gurwinder Singh
– Gurwinder Singh, Commented Oct 23, 2017 at 14:54
Thanks for you help! The problem is, I'm inheriting a couple of times from test. Do I have to have a FK relationship with all of the childen? — Roland Schützenhofer
– Roland Schützenhofer, Commented Oct 23, 2017 at 15:04
the relationship will be defined from one table - Profile with one table - Test. The child tables won't inherit it. — Gurwinder Singh
– Gurwinder Singh, Commented Oct 23, 2017 at 15:08

Isidro.rn · Accepted Answer · 2017-10-23 15:10:26Z

1

From PostgreSQL documentation:

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms of the above example:

https://www.postgresql.org/docs/9.1/static/ddl-inherit.html

So you have to declare explicitly any FK, they wont inherit

answered Oct 23, 2017 at 15:10

Isidro.rn

3242 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: Insert violates foreign key constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related