how to implement a foreign key type constraint against a not unique column - Mailing list pgsql-general

From Brent Wood
Subject how to implement a foreign key type constraint against a not unique column
Date
Msg-id 497842320200007B00018AC0@gwia1.ham.niwa.co.nz
Whole thread Raw
Responses Re: how to implement a foreign key type constraint against a not unique column  (Richard Broersma <richard.broersma@gmail.com>)
List pgsql-general
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.

pgsql-general by date:

Previous
From: Martijn van Oosterhout
Date:
Subject: Re: Custom type, operators and operator class not sorting/indexing correctly
Next
From: Richard Broersma
Date:
Subject: Re: how to implement a foreign key type constraint against a not unique column