Thread: 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
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.

On Wed, Jan 21, 2009 at 12:53 PM, Brent Wood <b.wood@niwa.co.nz> wrote:

> 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. 

This will work and yes it is a bit cumbersome but I don't think that
there is much else that can be done.

Another solution that is probably more cumbersome and ugly would be to
vertically partition your table and include all non -1 values in it.
Then use this table as the reference for your foreign key.  Then
create your own trigger to keep these two table in sync with each
other.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On 2009-01-21, Brent Wood <b.wood@niwa.co.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.

On Jan 25, 2009, at 3:07 AM, Jasen Betts wrote:

>> 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 ) );


anything wrong with create unique index foobar on foo where fee <> -1 ?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,497f50e9747031810420427!

On 2009-01-27, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:
> On Jan 25, 2009, at 3:07 AM, Jasen Betts wrote:
>
>>> 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 ) );
>
>
> anything wrong with create unique index foobar on foo where fee <> -1 ?

neither way works for a foreign key.

easiest solution seems to be to insert a record matching -1 in the
other table (with all other fields null)