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 ...
> And... I did some search on Google but can't figure "table-less unique
> indexes that multiple tables can share" where can I find further
> information regarding this thing?
Nowhere, I guess? At least in PostgreSQL, the index is always built on
top of a single table. So there's nothing like multi-column index or an
index without a table. And UNIQUE constraint requires an index.
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.
regards
Tomas
