Re: Re(2): REFERENCES fails on derived classes

On Mon, 11 Jun 2001, Michael Caine wrote:

> a month i was told that my possible bug submission was "sort of" a bug.
> the report and reply are below.
>
> now i write a follow up because i don't know how to establish a workroom.
> specifically, i know know that a unique constraint must be added to an
> inherited "id" field, even if it's a primary key in the base table.  i can
> do this by 'CREATE INDEX', but this creates a completely new constraint.
> i want to share the unique 'serial' behavior between the base table and
> derived table, so that en entry in either table is unique between the
> tables.  that way, when i do a 'SELECT baseTable*' kind of command, i can
> differentiate between two records.  is there a way to do this, or could
> this be considered a valid bug of some sort?

Unfortunately there's no way within the current constraints to do that
kind of unique constraint, although you should get actually unique numbers
out of the serial -- and those will span the two tables since it uses the
same sequence, you can't guarantee that explicitly placed values will be
unique. This is probably a bug, but inheritance needs alot of work in
general.

You might be able fake it with a insert/update trigger in plpgsql
that makes sure that there are no matching rows.  It wouldn't exactly
be the same thing as a unique constraint, but it'd probably be close
enough for most use.