CODEWITHSUNDEEP
postgresql
Roland Schützenhofer
Roland Schützenhofer

Reputation: 21

PostgreSQL: Insert violates foreign key constraint

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);

Upvotes: 0

Views: 7541

Answers (1)

Isidro.rn
Isidro.rn

Reputation: 314

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

Upvotes: 1

Related Questions