Is it possible in PostgreSQL to conditionally add a foreign key?
Something like:ALTER TABLE table1 ADD FOREIGN KEY (some_id) REFERENCES other_table WHERE some_id NOT IN (0,-1) AND some_id IS NOT NULL;
Specifically, my reference table has all positive integers (1+) but the table I need to add the foreign key to can contain zero (0), null and negative one (-1) instead, all meaning something different.
Notes:
I am fully aware that this is poor table design, but it was a clever trick built 10+ years ago when the features and resources we have available at this point did not exist. This system is running hundreds of retail stores so going back and changing the method at this point could take months which we don't have.
I can not use a trigger, this MUST be done with a foreign key.
You can add another "shadow" column to table1 which holds the cleaned values (i.e. everything but 0 and -1). Use this column for the referential integrity checks. This shadow column is updated/filled by a simple trigger on table1 which writes all values but 0 and -1 into the shadow column. Both 0 and -1 could be mapped to null.
Then you have reference integrity and your unchanged original column. The downside: You have also a little trigger and some redundant data. But alas, this is the fate of a legacy schema!