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.

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

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?