Thread: Error on reference to inherited primary key

Creating tables this way:

   A has a primary key
   B inherits A
   C references B

results in an error message on the CREATE TABLE for C (ERROR:  PRIMARY KEY for referenced table "b" not found).

To reproduce:

create table A
(
    id integer primary key
);

create table B
(
    dummy integer
) inherits (A);

create table C
(
    ref integer references B
);

psql:repro.sql:14: ERROR:  PRIMARY KEY for referenced table "b" not found

AFAIK the SQL code is correct. B should inherit the primary key from A, so C should be able to reference B.

The workaround appears to be explicitly naming the primary key column:
create table C
(
    ref integer references B(id)
);

I'm using PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96.

On Fri, 13 Apr 2001 rpijlman@spamcop.net wrote:

> Creating tables this way:
>
>    A has a primary key
>    B inherits A
>    C references B
>
> results in an error message on the CREATE TABLE for C (ERROR:  PRIMARY KEY for referenced table "b" not found).
>
> To reproduce:
>
> create table A
> (
>     id integer primary key
> );
>
> create table B
> (
>     dummy integer
> ) inherits (A);
>
> create table C
> (
>     ref integer references B
> );
>
> psql:repro.sql:14: ERROR:  PRIMARY KEY for referenced table "b" not found
>
> AFAIK the SQL code is correct. B should inherit the primary key from A, so C should be able to reference B.

Primary keys/Unique constraints do not currently inherit (they should, but
there are some questions about how they should).  If you want id to have
the properties of a primary key on B, you need to make a unique constraint
on B, I believe the NOT NULL is already inherited.

> The workaround appears to be explicitly naming the primary key column:
> create table C
> (
>     ref integer references B(id)
> );

This will not work for 7.1 since it checks for unique constraints (see
above)