inheritance and primary key contraint 
Author Message
 inheritance and primary key contraint

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);

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sat, 06 Nov 2004 08:33:21 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. SQL Droping Primary Key Contraints

2. Violation of PRIMARY KEY contraint error!!

3. Contraints on Primary keys

4. Alternative location for index from primary key contraint

5. Primary and Foreign Key Contraints

6. Adding a Primary Key Contraint

7. Primary key contraint when pushing

8. Primary Key Contraint violation problem

9. inheritance and primary/foreign keys

10. Inheritance and primary keys

11. Inheritance: problems with primary key

12. Composite Primary key or 1 primary key column with all values


 
Powered by phpBB® Forum Software