PostgreSQL foreign key references inherited table

Question

I am trying to simplify my code via inheritance. Here is what I have right now

DROP SCHEMA PUBLIC CASCADE;
CREATE SCHEMA PUBLIC;

CREATE TABLE student(
   id SERIAL PRIMARY KEY,
   age INT
);

CREATE TABLE engineer(
   id SERIAL PRIMARY KEY,
   studentId INT REFERENCES student (id),
   friend INT REFERENCES student (id)
);

CREATE TABLE artist(
   id SERIAL PRIMARY KEY,
   studentId INT REFERENCES student (id),
   friend INT REFERENCES student (id)
);

INSERT INTO student (age) VALUES (20); --Trying to remove this
INSERT INTO student (age) VALUES (21); --Trying to remove this
INSERT INTO engineer (studentId) VALUES (1);
INSERT INTO artist (studentId,friend) VALUES (2,1);

I don't want to have to type those two lines, therefore, I am using the below line

DROP SCHEMA PUBLIC CASCADE;
CREATE SCHEMA PUBLIC;

CREATE TABLE student(
   studentId SERIAL PRIMARY KEY,
   age INT
);

CREATE TABLE engineer(
   id SERIAL PRIMARY KEY,
   friend INT REFERENCES student (studentId)
) INHERITS (student);

CREATE TABLE artist(
   id SERIAL PRIMARY KEY,
   friend INT REFERENCES student (studentId)
) INHERITS (student);

INSERT INTO engineer (id,age) VALUES (DEFAULT,20);
INSERT INTO artist (id,age,friend) VALUES (DEFAULT,20,1);

But I get the following error.

psql:test.psql:45: ERROR:  insert or update on table "artist"     
violates foreign key constraint "artist_friend_fkey"
DETAIL:  Key (friend)=(1) is not present in table "student".

Is this yet another limitation to add to the long list of Postgres issues?

Craig Ringer · Accepted Answer · 2015-08-06 06:33:59Z

7

Yes. Look at docs Bottom Caveats section:

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

edited Aug 6, 2015 at 6:33

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

answered Aug 6, 2015 at 6:32

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

3 Comments

puk Over a year ago

Darth Vader Voice NOOOOOOOOOOOOoooooooooooooooooo

greg Over a year ago

In facts, Postgres inheritance should be considered as a structural trait. Very useful because when a trait is modified, all the tables using it are updated as well.

puk Over a year ago

@greg I don't speak Klingon =p care to dumb that down for me.

puk · Accepted Answer · 2015-08-06 15:33:27Z

1

I think I found a workaround for this. I just moved the reference into the parent like so

DROP SCHEMA PUBLIC CASCADE;
CREATE SCHEMA PUBLIC;

CREATE TABLE student(
   studentId SERIAL PRIMARY KEY,
   age INT,
   friend INT REFERENCES student (studentId)
);

CREATE TABLE engineer(
   id SERIAL PRIMARY KEY
) INHERITS (student);

CREATE TABLE artist(
   id SERIAL PRIMARY KEY
) INHERITS (student);

INSERT INTO engineer (id,age) VALUES (DEFAULT,20);
INSERT INTO artist (id,age,friend) VALUES (DEFAULT,20,1);

answered Aug 6, 2015 at 15:33

puk

16.9k32 gold badges126 silver badges209 bronze badges

Collectives™ on Stack Overflow

PostgreSQL foreign key references inherited table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related