Обсуждение: inherit with foreign key reference

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?

thanks,
aaron

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.

On Fri, 2005-05-06 at 15:51, 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"
> !----------------------------------------------------------!

Judging by the "pagesrc" error, it looks like you've edited the session
to remove any sensitive data, but in the doing may well have edited away
the evidence of the real problem.

What does the REAL session look like?

On Fri, May 06, 2005 at 01:51:45PM -0700, 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?
>
> thanks,
> aaron

You need to alter table Fooey to add fooid as UNIQUE NOT NULL
or PRIMARY KEY.  Then is will work fine.

See also: http://www.varlena.com/GeneralBits/106.php

--elein

>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>