写点什么

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

  • 2021-01-09
  • 本文字数:1106 字

    阅读完需:约 4 分钟

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]

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

2021-01-09 23:194621

评论

发布
暂无评论
发现更多内容

一文读懂 spring MVC 请求处理流程,java程序设计教程第三版

Java 程序员 后端

七、Redis持久化的两种方式RDB和AOF理解,mybatis接口实现原理

Java 程序员 后端

一文带你吃透Spring Cloud相关微服务组件及Spring Cloud Config框架

Java 程序员 后端

一辈子也不容错过学习的微服务网关与用户身份识别,Zuul过滤器

Java 程序员 后端

三面阿里被灵魂追问,出门就被Java面试官逼哭!,java虚拟机的运行原理

Java 程序员 后端

一线互联网大厂面经分享:阿里三面+头条四面(1),java基础入门第二版电子版

Java 程序员 后端

一文看透Java高并发:Synchronized锁的性质、原理及其缺陷

Java 程序员 后端

一线互联网大厂面经分享:阿里三面+头条四面,Java面试心得必备技能储备详解

Java 程序员 后端

一见面就脱裤子?这份Spring Cloud微服务笔记应对面试怎么这么骚?

Java 程序员 后端

一文彻底弄懂如何选择抽象类还是接口,java序列化和反序列化面试

Java 程序员 后端

一招教你搞定微信小程序-登录+支付(后台Java,windows内核编程全套视频教程

Java 程序员 后端

一文参透:缓存一致性策略以及雪崩、穿透等问题,java系统架构设计详解

Java 程序员 后端

三年Java开发每天增删改查,终于靠着这份面试题,成功上岸京东

Java 程序员 后端

三面美团Java岗,HR现场直接发offer,他是横着走出来的

Java 程序员 后端

一元稀疏多项式计算器 【 数据结构课设作业 】 带界面

Java 程序员 后端

一篇文章带你快速理解JVM运行时数据区 、程序计数器详解 (手画详图

Java 程序员 后端

上线GitHub七天后就标星87,并发知识体系大全

Java 程序员 后端

一个非常强大和友好的nginx基于lua-nginx-module(openresty)

Java 程序员 后端

【阿里Java岗的魔鬼三面】狠心刷完这6份pdf,Java开发经验谈

Java 程序员 后端

一个专科生和云计算的故事,java注解处理器工作原理及过程

Java 程序员 后端

一篇文章带你深入了解MySQL 索引相关,linux视频教程下载

Java 程序员 后端

【金九银十冲刺】Java岗面试题核心每日知识点,kafka原理图

Java 程序员 后端

一口气说出 Redis 16 个常见使用场景,rxjava原理

Java 程序员 后端

一篇文章!彻底弄透Java处理GMT-UTC日期时间,java百度天气接口api

Java 程序员 后端

一篇神文让你“一夜封神“Mycat 中间件 (最详细讲解),linux操作系统实用教程文东戈课后答案

Java 程序员 后端

三、Redis在SpringBoot中使用案例,java程序员面试笔试真题与解析

Java 程序员 后端

一个项目了解 SpringBoot 集成 MyBatis(1),面试必备知识点

Java 程序员 后端

一夜之间火爆GitHub的好文!!阿里资深架构师整理分享,疯狂膜拜

Java 程序员 后端

一文带你理解Spring Cloud高并发微服务架构核心理念的五脏六腑

Java 程序员 后端

三年Java开发经验,四面阿里成功斩获offer,分享面经,java面试问项目部署

Java 程序员 后端

一个即将从《蚂蚁金服》离职的Java工程师个人经历与总结

Java 程序员 后端

PostgreSQL 中如何启用/禁用及验证外键约束_语言 & 开发_Qunar技术沙龙_InfoQ精选文章