Re: unique across two tables

On 23/06/11 22.39, Tomas Vondra wrote:
> Dne 23.6.2011 20:39, Edoardo Panfili napsal(a):
>> I Have the same problem: one ID must be unique.
>> Three tables inherits from the same parent table the id column, the ID
>> is is defined as:
>> id bigint DEFAULT nextval('sequence_name')
>> in the parent table.
>>
>> Can I assume that a sequence ensures uniqueness?
>
> Well, not really. A sequence may be reset (so it will generate some
> values again) and some users (developers/DBAs) might use a value that
> did not come from the sequence (again, a duplicity).

> If you can somehow enforce that the sequence is never reset and that
> it's the only source of values, then it's probably safe. But the only
> way how to enforce that is to e-mail all the developers and DBAs with a
> threat that everyone who does not follow this rule will be executed ...
I also think it is so, also regarding "probably" safe :-)

> But there's a possible solution I guess - you can create a separate
> table with a single column (ID) with a UNIQUE constraint. And you can
> create AFTER trigger that attempts to update the table. That should
> provide exactly the same protection. It's elegant, it's reliable and I
> doubt you can implement a faster solution on your own.
I will try this solution in my database.

thanks to you and also to David
Edoardo