Re: inherit with foreign key reference

On Fri, 6 May 2005, Aaron Steele wrote:

> dear readers,
>
> i've created a simple Fooey table that inherits from Foo:
> !----------------------------------------------------------!
> CREATE TABLE Foo(
> fooid serial UNIQUE,
> footype text);
> CREATE TABLE Fooey(
> data text);
> INHERITS(Foo);
> !----------------------------------------------------------!
>
> next i try to create a Bar table that references Fooey's fooid (inherited from Foo) as a foreign key:
> !----------------------------------------------------------!
> CREATE TABLE Bar(
> fooeyid int REFERENCES Fooey(fooid));
> !----------------------------------------------------------!
>
> unfortunately i get the following error:
> !----------------------------------------------------------!
> ERROR: there is no unique constraint matching given keys for referenced table "pagesrc"
> !----------------------------------------------------------!
>
> thoughts?

Primary key, unique, and foreign key constraints do not play nicely with
inheritance. In the case above, what you're running into is the fact that
Fooey does not inherit Foo's unique constraint. You can explicitly make a
unique constraint on Fooey.fooid which would allow you to make the foreign
key (however note that references also do not inherit currently, so if you
were to make another table that inherits from Fooey, it would not play a
part in the constraint), however you might be expecting that values are
guaranteed unique between Foo and Fooey which would not be true, instead
each would be checked alone. This is a fairly serious deficiency with
inheritance right now.