PostgreSQL 11外键约束未生效的其他可能原因排查

问题背景 #

我们的PostgreSQL 11数据库包含表A和表B，执行以下查询及返回结果：

postgres=# \d A;
                              Table "public.A"
       Column        |            Type             | Collation | Nullable | Default 
---------------------+-----------------------------+-----------+----------+---------
 id                  | bigint                      |           | not null | 
 b_id                | bigint                      |           | not null | 
 
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
    "idx_a_bid" btree (b_id)
Foreign-key constraints:
    "fk_a_bid" FOREIGN KEY (b_id) REFERENCES A(id)

postgres=# select count(*) from A where b_id = 522039;
 count 
-------
    90
(1 row)
                  
postgres=# select count(*) from B where id = 522039;
 count 
-------
     0
(1 row)

表A中存在90条b_id为522039的数据，但表B中无id为522039的数据，且确认从未禁用过表的触发器，排除“有人禁用触发器并删除B表行”的情况，以下是其他可能的原因：

可能原因 #

• 外键约束定义错误：从\d A的输出能直接看到，外键fk_a_bid实际关联的是A(id)，而非B(id)——这是核心问题。这个外键是表A的自引用约束，和表B完全无关，所以表B的行操作不会触发任何针对A.b_id的检查，只要A.b_id的值存在于A.id中就符合约束。
• 数据导入时绕过约束：使用COPY ... DISABLE_TRIGGER命令导入数据，或者通过pg_dump导出时包含SET session_replication_role = replica;语句，导入过程会跳过外键约束检查，即使约束存在，也能导入不符合规则的数据。
• 约束被修改过：如果最初外键确实指向B(id)，但后续被删除后重新创建时写错了关联表，就会出现当前的不一致情况。
• 表B为分区表的查询盲区：若表B是分区表，且id不是分区键，查询count(*) from B where id=522039可能未扫描到包含该数据的分区（比如分区未正确挂载、查询计划未覆盖所有分区），导致返回0。可以通过SELECT * FROM B WHERE id=522039并查看执行计划验证。
• 约束为NOT VALID状态：如果外键约束是用NOT VALID选项创建的，PostgreSQL仅对新插入/更新的数据做约束检查，不会验证已存在的数据。即使后续执行了VALIDATE CONSTRAINT，若验证过程中数据发生变化，也可能出现不一致，但这种情况的前提是创建约束时已有不符合的数据。

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