From: | Edoardo Panfili <edoardo(at)aspix(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: unique across two tables |
Date: | 2011-06-24 05:44:42 |
Message-ID: | 4E04244A.9070301@aspix.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2011-06-24 06:09:28 | Re: Oracle / PostgreSQL comparison... |
Previous Message | Uwe Schroeder | 2011-06-24 04:59:03 | Re: Oracle / PostgreSQL comparison... |