Thread: inheritance and foreign keys

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

"Thomas F. O'Connell" wrote:
  >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?

Your problem is that the primary key constraint has not been inherited,
so sub_foo does not have a primary key.

You need to do:

create table foo (id int2 primary key);
create table sub_foo (primary key (id)) inherits(foo);


--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Do not be anxious about anything, but in everything,
      by prayer and supplication, with thanksgiving, present
      your requests to God. And the peace of God, which
      transcends all understanding, will guard your hearts
      and your minds in Christ Jesus."   Philippians 4:6,7