Thread

Re: Inheritance and constraints duplicate values

Oliver Elphick <olly@lfix.co.uk> — 2004-08-24T19:34:06Z

On Sat, 2004-08-21 at 12:44, Marian Lojka wrote:
> After doing some research, this is apparently a known long-standing
> issue with inheritance in Postgres. Nobody calls it a "bug" outright,
> though. Just a limitation. It makes me wonder how others have tackled
> problems of a similar nature.

> CREATE TABLE products (
> id serial NOT NULL PRIMARY KEY,
> name varchar(64) NOT NULL,
> price numeric(6,2)
> );
> 
> CREATE TABLE pants (
> waist smallint,
> length smallint,
> colour varchar(12)
> ) inherits (products);
> 
> CREATE TABLE computers (
> cpu varchar(12),
> mhz smallint,
> ) inherits (products);
> 
> INSERT INTO pants (name,price,waist,length,colour)
> VALUES ('Brand-X Cargo Pants', 49.95, 32, 34, 'khaki');
> 
> INSERT INTO computers (name,price,cpu,mhz)
> VALUES ('Flower Power iMac', $666.66, 'Motorola 750', 500);

> INSERT INTO computers (id, name,price,cpu,mhz)
> VALUES (1, 'Mac Plus', $5.00, 'Motorola 68000', 8);
> 
> SELECT id,name FROM products;
> 
> --> 1 Brand-X Cargo Pants
> --> 1 Mac Plus
> --> 2 Flower Power iMac
> 
> 
> Huh? But products.id is a primary key! The "Mac Plus" screwed it up by
> inserting duplicate values!

Unfortunately it's only a primary key in the parent table; that
constraint is not inherited.

To implement foreign keys on an inheritance hierarchy would require
indexes that could index multiple tables.  As far as I know, that is not
currently possible.

The work-around is to create another table

  CREATE TABLE product_xref (
	id serial PRIMARY KEY
  );

then 

  ALTER TABLE products ADD CONSTRAINT FOREIGN KEY (id) references
product_xref(id);

(and id in products should not be a serial field, or the default should
be explicitly set to nextval(product_xref_id_seq))

and the same for each child table

Then create a trigger function to create a record in product_xref on
insertion and delete it on deletion.

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "I saw in the night visions, and, behold, one like the 
      Son of man came with the clouds of heaven, and came to
      the Ancient of days, and they brought him near before 
      him. And there was given him dominion, and glory, and 
      a kingdom, that all people, nations, and languages, 
      should serve him; his dominion is an everlasting 
      dominion, which shall not pass away, and his kingdom 
      that which shall not be destroyed."     
                                    Daniel 7:13,14