Foreign keys + table inheritance in PostgreSQL?

Question

I have three tables: organization, organization_teams and org_users. Here organization_teams is inherited from organization. So suppose if a record is added in organizations_teams it will get the organization table id as value for id column in organization_teams.

org_users has foreign key on id column of organization. Now when I try to insert data in org_users it giving me error as below

insert or update on table "org_users" violates foreign key constraint "org_users_organizations"
DETAIL:  Key (org_id)=(12) is not present in table "organizations"

Why?

This is known caveat: 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 postgresql.org/docs/current/static/… — pozs
– pozs, Commented Jun 23, 2014 at 7:18
i already have some organizations in Organization tables and some in organization_teams,So org_user have foreign key on organizations. — Supriya Pansare
– Supriya Pansare, Commented Jun 23, 2014 at 7:31

Craig Ringer · Accepted Answer · 2014-06-23 10:31:38Z

43

It's covered in the user manual.

The short version: you can use foreign keys, or table inheritance, but not both. This isn't inherently impossible, it's just that it's technically quite difficult to implement unique indexes that span inherited tables in PostgreSQL in a fast, reliable manner. Without that, you can't have a useful foreign key. Nobody's successfully implemented it well enough for a patch adding support to be accepted into PostgreSQL yet.

A foreign key can point to a table that is part of an inheritance hierarchy, but it'll only find rows in that table exactly. Not in any parent or child tables. To see which rows the foreign key sees, do a SELECT * FROM ONLY thetable. The ONLY keyword means "ignoring inheritance" and that's what the foreign key lookup will do.

edited Jun 23, 2014 at 10:31

answered Jun 23, 2014 at 8:44

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

2 Comments

Andrew Wolfe Over a year ago

Would it be 'safe' to only apply foreign keys at the 'leaf' tables of a table inheritance hierarchy?

Craig Ringer Over a year ago

@AndrewWolfe If referencing and referenced tables are partitioned on the same key you can have FKs between corresponding partitions. That's about all.

shcherbak · Accepted Answer · 2017-06-07 21:09:33Z

4

The only workaround i have found is:

create function returning trigger for checking existence of some id in inherited table
create a constraint trigger instead of FK

answered Jun 7, 2017 at 21:09

shcherbak

7888 silver badges14 bronze badges

Comments

Anil Agrawal · Accepted Answer · 2019-10-09 09:14:08Z

1

Completely agree with @Craig Ringer, we can't use foreign keys along with inheritance.

But if we are sure that we are going to insert correct data, and we want to use foreign key in org_users, we can do one thing.

We can create a child table of org_users say org_users_child (with no foreign key) as below

CREATE TABLE org_users_child () INHERITS (org_users);

Now we can insert data in this child table.

Now if we query over org_users table, we can find desired results.

answered Oct 9, 2019 at 9:14

Anil Agrawal

3,0361 gold badge29 silver badges34 bronze badges

Collectives™ on Stack Overflow

Foreign keys + table inheritance in PostgreSQL?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related