Re: [GENERAL] what about uniqueness of inherited primary keys

[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