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
incolumn 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,
thena 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.