Thread

1. Error on reference to inherited primary key

   Rene Pijlman <rpijlman@spamcop.net> — 2001-04-13T22:40:56Z

   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.
   
   

2. Re: Error on reference to inherited primary key

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-04-14T18:50:09Z

   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)