On 2009-01-21, Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz> wrote:
> Hi,
>
> I have a table with a column of ID's (integer), these are unique
> except where they = -1 (column 1)
> I have a partial unique index where the value is not -1 to enforce this.
> I want to use this column as a foreign key on a column in another table
> (column 2), but cannot without a full unique index.
a full unique index is easy use an expression that's null for -1.
create unique index foobar on foo( ( case fee when -1 then null else fee end ) );
> Is there any way to add an equivalent constraint to a foreign key which
> restricts entries in column 2 to values in column 1?
unfortunately expressions don't seem to be allowed for foreign key constraints
you could add an auxillary column and create a rule, or trigger to keep
it updated.