如何为存在孤立数据的PostgreSQL数据库添加外键约束？

核心方案是在添加外键时使用NOT VALID选项，它会跳过对现有数据的约束校验，仅对后续的插入、更新操作生效。

正确的SQL语句：

ALTER TABLE foo
ADD CONSTRAINT foo_bar_id_fk
FOREIGN KEY (bar_id)
REFERENCES bar (id)
ON DELETE CASCADE
NOT VALID;

为什么之前的DEFERRABLE不生效？ #

DEFERRABLE INITIALLY IMMEDIATE仅控制约束的检查时机（事务内延迟或立即检查），但添加约束的操作本身会强制校验所有现有数据，所以即便用了这个选项，只要存在孤立数据就会触发报错。

后续可选操作 #

如果之后想要清理现有孤立数据并让约束完全覆盖所有数据，可以执行：

ALTER TABLE foo VALIDATE CONSTRAINT foo_bar_id_fk;

这条语句会后台校验现有数据是否符合约束，PostgreSQL 9.4及以上版本支持校验期间不锁表，校验完成后约束就会对所有数据生效。

内容的提问来源于stack exchange，提问作者Mitya