Conditional PostgreSQL foreign key

Question

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.

Answer 1

The short answer is no, Postgres does not have conditional foreign keys. Some options you might consider are:

1. Just not have a FK constraint. Move this logic into the data access layer and live without the referential integrity.
2. Allow NULL in the column, which is perfectly valid even with a FK constraint. Then, use another column to store whatever the meaning of 0 and -1 is.
3. Add a dummy row in the referenced table for 0 and -1. Even if it just had bogus data, it would satisfy the FK constraint.

Hope this helps!