what about uniqueness of inherited primary keys

[Andreas]

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 ?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Scott Ribe]

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
sc********@killerbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Oliver Elphick]

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 Ol************@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Seastian Böck]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Andreas]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Sebastian Böck]

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Chris Travers]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org