Thread: what about uniqueness of inherited primary keys
Hello list, what about uniqueness of inherited primary keys ? eg you have : create table objects ( id int4, date_created timestamp(0), primary key (id) ); create table persons ( firstname varchar(100), lastname varchar(100) ) inherits (objects); now ... insert into objects (id) values (1); A repetition of this line would cause an unique-constraint error of objects_pkey. insert into persons (id, firstname, lastname) values (1, 'Super', 'Man'); insert into persons (id, firstname, lastname) values (1, 'Bat', 'Man'); works though it violates the inherited constraint. A select * from objects; shows id=1 three times. delete * from objects; empties both tables. after ALTER TABLE public.persons ADD CONSTRAINT persons_pkey PRIMARY KEY(id); I can still insert at least one record with id=1 in each table. select count(*) from objects where id=1; results 2 Shouldn't we expect to have unique entries in a primary key collumn ?
I've been told that not all features of inheritance are implemented. What this means in practice is that you often have to redeclare constraints on inherited tables. So in your example you'd need to alter table persons and add the constraint, but even then I think it might be possible to insert an objects row and a person row with the same id. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
On Sun, 2003-12-28 at 20:17, Andreas wrote: > what about uniqueness of inherited primary keys ? ... > Shouldn't we expect to have unique entries in a primary key collumn ? We should, but it doesn't happen. This is a very long-standing defect which has not yet been solved. To solve it would need an index attached to multiple tables and the mechanism for that does not yet exist. The same applies to foreign keys; these too are not inherited. The solution for primary keys is something like this: CREATE TABLE tree_index (tboid oid, id varchar(10),primary key (id),unique (tboid, id)); CREATE TABLE person (id varchar(10) primary key, surname text,foreign key (tableoid, id) references tree_index(tboid, id) match full on update cascade on delete cascade); CREATE TABLE parent (eldest_child varchar(10), primary key (id),foreign key (tableoid, id) references tree_index(tboid, id) match full on update cascade on delete cascade) inherits (person); CREATE TABLE child (father varchar(10) references parent(id), mother varchar(10) references parent(id), primary key (id), foreign key (tableoid, id) references tree_index(tboid, id) match full on update cascade on delete cascade) inherits (person); alter table parent add constraint "child key" foreign key (eldest_child) references child (id) on update cascade on delete restrict; Then you need a trigger on each table to create a record in tree_index when a new record is added to a table in the hierarchy. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "For God shall bring every work into judgment, with every secret thing, whether it be good, or whether it be evil." Ecclesiastes 12:14
Andreas wrote: > Hello list, > > what about uniqueness of inherited primary keys ? > > eg you have : > > create table objects ( > id int4, > date_created timestamp(0), > primary key (id) > ); > > create table persons ( > firstname varchar(100), > lastname varchar(100) > ) inherits (objects); > now ... > > insert into objects (id) values (1); > A repetition of this line would cause an unique-constraint error of > objects_pkey. > > insert into persons (id, firstname, lastname) values (1, 'Super', 'Man'); > insert into persons (id, firstname, lastname) values (1, 'Bat', 'Man'); > works though it violates the inherited constraint. > > A select * from objects; shows id=1 three times. > > delete * from objects; empties both tables. > > after ALTER TABLE public.persons ADD CONSTRAINT persons_pkey PRIMARY > KEY(id); > I can still insert at least one record with id=1 in each table. > > select count(*) from objects where id=1; > results 2 > > Shouldn't we expect to have unique entries in a primary key collumn ? Hello Andreas, for primary keys there is a simple (and at least working for me) solution as long as you can use the SERIAL type for your primary key. CREATE TABLE objects ( id SERIAL PRIMARY KEY, date_created timestamp(0) ); CREATE TABLE persons ( id INTEGER PRIMARY KEY, firstname varchar(100), lastname varchar(100) ) inherits (objects); Now the id column gets merged and you should have the desired behaviour. If you want objects.id to get referenced by other tables you have to work around with triggers and an extra table. For persons.id everything is working fine. This solution (workaround) is only working as long you don't insert id-values without updating the corresponding sequence. HTH Sebastian
Seastian Böck wrote: > for primary keys there is a simple (and at least working for me) > solution as long as you can use the SERIAL type for your primary > key. > [...] > Now the id column gets merged and you should have the desired > behaviour. > > If you want objects.id to get referenced by other tables you have > to work around with triggers and an extra table. For persons.id > everything is working fine. > > This solution (workaround) is only working as long you don't > insert id-values without updating the corresponding sequence. Hello Se(b)astian -- you left out the 'b' in your e-mail setup ;) right, your proposal does in a way behave like I wanted. Though the idea of integrity control by the db-server is still not there for parent id-colomns. Every user or application could mess up the primary key of the inherited table. That spoils a bit of the oo-approach, I fear. It wouldn't be that bad, if the table contents weren't merged in SELECTs. Probaply one could do some trigger-magic to check the inserted id against an id-pool in another table. If one knew anything about triggers that is ... well ... miles to go before I sleep ... Thanks Andreas
Andreas wrote: > Seastian Böck wrote: > >> for primary keys there is a simple (and at least working for me) >> solution as long as you can use the SERIAL type for your primary >> key. >> [...] >> Now the id column gets merged and you should have the desired >> behaviour. >> >> If you want objects.id to get referenced by other tables you have >> to work around with triggers and an extra table. For persons.id >> everything is working fine. >> >> This solution (workaround) is only working as long you don't >> insert id-values without updating the corresponding sequence. > > > > Hello Se(b)astian > -- you left out the 'b' in your e-mail setup ;) > > right, your proposal does in a way behave like I wanted. Though the idea > of integrity control by the db-server is still not there for parent > id-colomns. Every user or application could mess up the primary key of > the inherited table. That spoils a bit of the oo-approach, I fear. I rechecked that and the conclusion is very simple: it only works reliable if the id is autogenerated by the SERIAL type. > > It wouldn't be that bad, if the table contents weren't merged in SELECTs. > > Probaply one could do some trigger-magic to check the inserted id > against an id-pool in another table. > If one knew anything about triggers that is ... well ... miles to go > before I sleep ... For all other situations take a look at Oliver's mail. Sebastian
Hi all; The real problem with using the trigger approach is as follows: Normally the unique constraint is handled by an index (most efficient way), but doing so using a trigger is more likely to lose this benefit and give you some performance problems. IIRC, the problems with inherited keys are, at present, the fact that inherited indexes pose some problems. Again, custom triggers would solve that problem perhaps at a performance cost. Best Wishes, Chris Travers