Thread

1. Foreign key constraints on inherited columns, 2 errors.

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-05-16T09:18:10Z

   Stephen Deasey (sdeasey@bigfoot.com) reports a bug with a severity of 2
   The lower the number the more severe it is.
   
   Short Description
   Foreign key constraints on inherited columns, 2 errors.
   
   Long Description
   Using the Postgres 7.1.1 RPMs I typed the following into psql:
   
   =# create table foo (foo integer);
   CREATE
   =# create table bar (bar integer) inherits(foo);
   CREATE
   =# create table baz (baz integer references bar(foo));
   NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
   ERROR:  UNIQUE constraint matching given keys for referenced table "bar" not found
   
   
   I think this is incorrect, and it is not the behaviour I was seeing with version 7.0.3 where it worked as expected.
   
   
   HOWEVER...
   
   I upgraded to 7.1.1 in the first place hoping to fix the following problem:  The above defined tables load correctly. Table bar has data loaded into the inherited columns from foo, which can clearly be seen when selecting using the 'from bar*' syntax. When inserting into baz the referential constraint kicks in complaining there is no matching value in bar. The constraint trigger is not checking up the hierarchy of inherited columns.
   
   Sorry I can't give the exact error mesage now, I blew away the old installation.
   
   
   Thanks!
   
   Sample Code
   
   
   No file was uploaded with this report
   
   
   

2. Re: Foreign key constraints on inherited columns, 2 errors.

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-05-16T17:23:12Z

   On Wed, 16 May 2001 pgsql-bugs@postgresql.org wrote:
   
   > Stephen Deasey (sdeasey@bigfoot.com) reports a bug with a severity of 2
   > The lower the number the more severe it is.
   > 
   > Short Description
   > Foreign key constraints on inherited columns, 2 errors.
   > 
   > Long Description
   > Using the Postgres 7.1.1 RPMs I typed the following into psql:
   > 
   > =# create table foo (foo integer);
   > CREATE
   > =# create table bar (bar integer) inherits(foo);
   > CREATE
   > =# create table baz (baz integer references bar(foo));
   > NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
   > ERROR:  UNIQUE constraint matching given keys for referenced table "bar" not found
   > 
   > 
   > I think this is incorrect, and it is not the behaviour I was seeing
   > with version 7.0.3 where it worked as expected.
   
   No, actually it didn't really.  If bar had non-unique values the 
   references constraint would do fairly random bad things.  SQL 
   requires the unique constraint on the columns, and so do we now.
   Just add a UNIQUE(foo) to bar's definition and it should work.