how to implement a foreign key type constraint against a not unique column

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. Is there any way to add an equivalent constraint to a foreign key which restricts entries in column 2 to values in column 1?

I tried a check where obs_id in (select id from ..), but subqueries are not supported in a check.

I believe it is possible by using a table with nulls for the -1 values with a unique index on it as the foreign key, then a view which uses case or coalesce to present the nulls as -1, but this seems a cumbersome workaround.

Thanks,

Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.