Thread: Foreign key constraints on inherited columns, 2 errors.
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. Tablebar 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. Theconstraint 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
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.