PostgreSQL 中如何启用/禁用及验证外键约束

1 问题提出

近期在做多个数据源 DB 的数据向一个目标 DB 做数据迁移的过程中，遇到有外键约束的表，由于表之间数据的依赖关系和数据的导入顺序导致数据加载失败，因此记录了一下关于这类问题的解决思路。

testa 中 t1 表结构及数据如下：

testb 中 t2 表结构及数据如下：

当恢复数据时，由于数据导入顺序问题，可能会出现违反约束的报错。

比如，首先恢复的 t2 表中数据，此时会有如下的报错：

2 分析问题

这里数据本身并没有问题，只是当时恢复表数据时候首先恢复了 t2 表导致了违反外键约束的报错，此时我们可以首先禁用 t2 表上的外键约束并在加载对其进行验证。

这里 all 会禁用表上的所有外键，同时也禁用负责验证约束的内部触发器，这里使用 all 也存在一些限制，就是你必须是超级用户才能执行此操作，如果是普通用户执行将失败。如果你想要使用普通用户去禁用某一特定用户的用户触发器。

此时，我们再对表 t2 进行数据插入可以成功：

此时，我们首先插入一条违反外键约束的数据，然后将约束条件启用：

此时成功启用约束条件，但是没有报错不符合外键约束！那我再向 t2 插入一次数据，验证约束是否真的生效。

由此可以验证，验证约束的内部触发器已经启用，对于后续新插入或者更新的数据会进行检查。那么对于之前的行，为什么没有进行检查呢？

通过查看系统表 pg_constraint，我们可以看到约束记录的状态为已验证。

3 解决问题

那么我们应该如何处理数据恢复中的外键约束问题并且使新数据和老数据都进行有效性验证呢？

方法 1：删除外键

• 删除表中的所有外键
• 加载数据
• 重新创建外键，但使其无效，以避免增大 cost，现在数据会被验证
• 当系统功能负载较小时，验证约束

这样的方式，创建无效外键对后续新插入和更新的数据有约束作用，在外键检查时对之前存在的数据可以进行检查，从而保证所有的数据符合外键约束。

方法 2：修改系统表状态

通过上面的测试我们可以知道，在对约束禁用期间，约束记录的状态为已验证，此时我们可以直接更新系统表 pg_constraint。

在这种情况下，这个约束将被完全验证，因为他在系统表中记录为无效。

方法 3：约束延迟生效

这样做的缺点是它仅在一个事务中生效。因此，必须在事务中保证各表之间数据的约束。

4 小结

迁移数据时，如果涉及外键约束的多个表的导入（尤其来自多个数据源,原始数据不一定满足约束关系），灵活启用/禁用及验证外键约束，可使迁移后合并的数据切实满足外键约束，保证 DB 中的关系完整性。

头图：Unsplash

作者：彭占元

原文：https://mp.weixin.qq.com/s/Lom04TPWlSse-hEqRuSV_Q

原文：PostgreSQL 中如何启用/禁用及验证外键约束

来源：Qunar 技术沙龙 - 微信公众号 [ID：QunarTL]

转载：著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。