Re: what about uniqueness of inherited primary keys - Mailing list pgsql-general

From Sebastian Böck
Subject Re: what about uniqueness of inherited primary keys
Date
Msg-id 3FF0661B.7020903@freenet.de
Whole thread Raw
In response to Re: what about uniqueness of inherited primary keys  (Andreas <maps.on@gmx.net>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Michael Glaesemann
Date:
Subject: Re: Time varying referential integrity
Next
From: Andreas
Date:
Subject: Re: simple auto-updating timestamp ?