Foreign keys + table inheritance in PostgreSQL?
Solution 1
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.
Solution 2
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
![Supriya Pansare](https://i.stack.imgur.com/w5Nn7.jpg?s=256&g=1)
Supriya Pansare
Updated on June 07, 2022Comments
-
Supriya Pansare about 2 years
I have three tables:
organization
,organization_teams
andorg_users
. Hereorganization_teams
is inherited fromorganization
. So suppose if a record is added inorganizations_teams
it will get theorganization
tableid
as value forid
column inorganization_teams
.org_users
has foreign key onid
column oforganization
. Now when I try to insert data inorg_users
it giving me error as belowinsert 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?
-
Andrew Wolfe over 8 yearsWould it be 'safe' to only apply foreign keys at the 'leaf' tables of a table inheritance hierarchy?
-
Craig Ringer over 8 years@AndrewWolfe If referencing and referenced tables are partitioned on the same key you can have FKs between corresponding partitions. That's about all.