Re: unique across two tables

> On 22/06/11 18.30, David Johnston wrote:
> > The only (obvious to me) way to really solve the problem - invisibly -
> > is to allow for table-less unique indexes that multiple tables can
> > share and that have a pointer to the "source" table for any particular
entry
> in the index.
> > The other method being discussed effectively uses a physical table to
> > implement this behavior.
>
> 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?
>
> 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?
>
> thank you
> Edoardo

A sequence simply maintains a "next number to issue" variable. Using
"nextval('seq_name') you can get that value AND have the value incremented
by 1. However, you can directly change that value and it will happily
continue on as normal. If you "reset" the sequence to a number less-than
the current "next number to issue" you end up having a "potential" duplicate
- what matters in determining if you "actually" have a duplicate is how you
then use that value. In your case you would end up with different records
sharing the same ID unless they happen to fall onto the same table and a
UNIQUE index prevents the insert.

You could wrap the call to nextval('seq_name') in a SECURITY DEFINER
function and lock down the sequence to normal users but unless PostgreSQL
has some built-in mechanism to make a sequence "FORWARD-ONLY" what you
suggest is not without risk.

"table-less unique indexes..." are not a "thing" that I know of - it is more
of a description on my part. PostgreSQL requires that an index be attached
to a single table and thus the table to which the index pointer "points" is
inferred from that relationship. A "table-less" index would not have such
an implicit relationship and would need to store the table to which indexed
"record" belongs. While the concept sounds good to me I have no idea how a
traditional index is technically coded and functions...

David J.