PostgreSQL Conditional Foreign Key

Question

Is it possible to conditionally add a foreign key in PostgreSQL?

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;

In particular, my reference table has all positive integers (1+), but the table in which I have to add the foreign key can contain zero (0), zero and negative (-1), and everything means something else.

Notes:

I fully understand that this is a poor table design , but it was a tricky trick created 10 years ago, when the opportunities and resources that we had available at the moment did not exist. This system works by hundreds of retail stores, so going back and changing the method at this point can take months that we don’t have.

I can’t use the trigger, it MUST be done using a foreign key .

Answer 1

You can add another “shadow” column to table1 , which contains cleared values ​​(i.e. everything except 0 and -1 ). Use this column to verify referential integrity. This shadow column is updated / populated with a simple trigger on table1 , which writes all values, but 0 and -1 to the shadow column. Both 0 and -1 can be mapped to null .

Then you have link integrity and an immutable source column. Disadvantage: you also have a small trigger and some redundant data. But, alas, this is the fate of the inherited scheme!

Answer 2

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

• It just has no FK limit. Move this logic to the data access layer and live without referential integrity.
• Allow NULL in a column that is fine even with an FK constraint. Then use another column to store the values 0 and -1 .
• Add a dummy row in the reference table for 0 and -1 . Even if he had fake data, he would satisfy the FK constraint.

Hope this helps!

Answer 3

Your requirement is equivalent to this check constraint:

 create table t (a float check (a >= -1 and a = floor(a) or a is null)); 

Answer 4

Here is another opportunity. Use PG Inheritance to force a table section to +1 in the flag column, and otherwise. (Normal rules / triggers to maintain this.) Then, the FK link between the Has_PLUS_ONE child table and the reference table.

Answer 5

You can implement this with a check constraint and a foreign key.

 CREATE TABLE table1 (some_id INT, some_id_fkey INT REFERENCES other_table(other_id), CHECK (some_id IN (0,-1) OR some_id IS NOT DISTINCT FROM some_id_fkey)); 

(not verified)