inheritance and primary key contraint

From: david(dot)butcher(at)cathaynetworks(dot)com
To: pgsql-general(at)postgresql(dot)org
Cc: david(dot)butcher(at)cathaynetworks(dot)com
Subject: inheritance and primary key contraint
Date: 2002-05-19 19:44:07
Message-ID: 1021837447.3ce800874e076@sinclair.swishmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The primary key constraint does not seem to be enforced across inheritance.

Page 69 7.2 referennce manual
"unique contraints and primary keys are not inherited in the current
implementation. This makes the combination of unique constraints and inheritance
rather dysfunctional'.

In the following example, we are able to insert 2 rows with identical primary
keys - one on the base table and one on the derived table. Both examples exhibit
the same behavior.

What can we do to enforce uniqueness over the whole tree?

CREATE TABLE Base( baseId integer, PRIMARY KEY ( baseId ) );
CREATE TABLE Derived () INHERITS (Base);
insert into Derived (baseId) values (1);
// this insert should fail, but does not
insert into Base (baseId) values (1);

CREATE TABLE Base( baseId integer, PRIMARY KEY ( baseId ) );
CREATE TABLE Derived (PRIMARY KEY (baseId)) INHERITS (Base);
insert into Derived (baseId) values (1);
insert into Base (baseId) values (1);
// this insert should fail, but does not
insert into Base (baseId) values (1);

Browse pgsql-general by date

  From Date Subject
Next Message Joseph Maxwell 2002-05-19 19:46:33 Digest Mode
Previous Message Patrice Hédé 2002-05-19 19:14:42 Re: [HACKERS] UTF-8 safe ascii() function