在Postgresql中有可能有条件地添加外键吗？
类似于：ALTER TABLE table1 ADD FOREIGN KEY(some_id)REFERENCES other_table WHERE some_id NOT IN(0,-1)AND some_id IS NOT NULL;
具体来说,我的引用表具有所有正整数(1)但是我需要添加外键的表可以包含零(0),空和负一(-1),所有这些都意味着不同的东西.
笔记：
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.
您可以向table1添加另一个“shadow”列,该列保存已清理的值(即除了0和-1之外的所有值).使用此列进行参照完整性检查.此影子列由table1上的简单触发器更新/填充,该触发器将除0和-1之外的所有值写入影子列. 0和-1都可以映射为null.
然后,您具有参考完整性和未更改的原始列.缺点：您还有一点触发器和一些冗余数据.但唉,这是传统架构的命运！
