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

On Wed, Jan 21, 2009 at 12:53 PM, Brent Wood <b(dot)wood(at)niwa(dot)co(dot)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, then a 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