同表中可延迟主键与自引用外键创建报错问题咨询

问题场景 #

创建了带DEFERRABLE INITIALLY DEFERRED主键的临时表，并插入互相引用的数据：

CREATE TEMP TABLE t
( id integer
 ,txt text
 ,base integer
 ,CONSTRAINT t_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO t VALUES
 (1, 'one', 2)
,(2, 'two', 1);

尝试添加自引用外键时触发报错：

ERROR:  cannot use a deferrable unique constraint for referenced table "t"

原因说明 #

PostgreSQL的外键约束要求被引用的唯一/主键约束必须是**不可延迟（IMMEDIATE）**的。这是因为可延迟约束的检查时机在事务提交阶段，而自引用外键的一致性检查需要在数据操作过程中确保引用关系有效，可延迟约束的动态检查逻辑会导致数据库无法稳定维护外键规则，因此存在该隐性限制。

解决方案 #

步骤1：临时修改主键为不可延迟 #

先将主键约束改为不可延迟，为创建外键做准备：

ALTER TABLE t ALTER CONSTRAINT t_pkey NOT DEFERRABLE;

步骤2：创建自引用外键 #

执行外键创建语句：

ALTER TABLE t ADD CONSTRAINT t_base_fkey FOREIGN KEY (base) REFERENCES t (id);

步骤3：恢复主键的可延迟属性（可选） #

如果业务需要保留主键的可延迟特性，创建外键后再改回原配置：

ALTER TABLE t ALTER CONSTRAINT t_pkey DEFERRABLE INITIALLY DEFERRED;

执行以上步骤后，外键约束即可正常创建，同时原有数据的互相引用关系也会被外键约束校验。

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