Re: Foreign keys don't work with inherited tables

On Sat, 23 Jun 2001, Micah Yoder wrote:

> Using PostgreSQL 7.1.2 compiled with Red Hat 7.1's gcc 2.96.
>
> I have a database that uses inherited tables. The top level table of the
> hierarchy has a serial column, which is the primary key. Everything inserted
> into any of its decendents and each shares the primary key and sequence of
> the top table. That works fine.
>
> Another table (outside the hierarchy) uses a foreign key to the top level
> primary key. Inserting into this table works when the foreign key references
> an item that is in the TOP level class of the hierarchy. But when it
> references a subclass, I get a "referential integrity violation".
>
> Attached is a file with everything you should need to see what's up.
>
> Please let me know if you need more info, and if there are any workarounds.
> I guess I could not use a foreign key there but that would kind of suck ...

Yes. This doesn't work (see long discussions about this on -general,
-hackers and -bugs in the archives) and probably won't work until
inheritance gets a serious looking at. The only workaround we've seen is
having a separate id table with each of the inherited tables referencing
into it for their ids and other tables reference the id table rather than
the inheritance tree.

Side Note: Unless you define a primary key on those inherited tables they
do not share the primary key of the parent, although they do share the
default (for the sequence). In addition, even if you do put a primary key
on each of them, they're separate keys, so you can end up with duplicated
values in different trees if you ever insert explicit values into tables.