Re: inheritance and foreign keys

"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(dot)Elphick(at)lfix(dot)co(dot)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