inheritance and foreign keys

From: "Thomas F(dot) O'Connell" <tfo(at)monsterlabs(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: inheritance and foreign keys
Date: 2001-04-06 19:45:46
Message-ID: 3ACE1CEA.8060301@monsterlabs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

this might actually involve two separate issues. one is empty
inheritance (which i'll describe in a second); the other is inheritance
and foreign keys.

here is a description of what i want to do:

i want to create a table as a super-class. then i want to create a
sub-class of that table that has exactly the same column set. then i
want to create another table that has a foreign key that references a
column in the sub-class that is a primary key of the super-class.

here is a basic psql sequence that is what i am attempting:

$psql test_db
test_db=# create table foo( id int2 primary key );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey'
for table 'foo'
CREATE
test_db=# create table sub_foo() inherits( foo );
CREATE
test_db=# create table bar( sub_foo_id int2 references sub_foo( id ) );
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR: UNIQUE constraint matching given keys for referenced table
"sub_foo" not found

why do i get the ERROR at the end of that sequence of commands? i don't
understand why there would be a failed UNIQUE constraint matching or
even where that is actually occurring. if i add UNIQUE to bar above, i
get the same error.

is it not possible to reference the keys of a sub-classed table directly
from a foreign table?

-tfo

Browse pgsql-general by date

  From Date Subject
Next Message Scott Gritton 2001-04-06 19:47:17 Convert Filemaker Pro db to Postgresql db
Previous Message Doug McNaught 2001-04-06 18:18:28 Re: quota's ?