AICon上海|与字节、阿里、腾讯等企业共同探索Agent 时代的落地应用 了解详情
写点什么

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:194661

评论

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

构建高可用性的 SQL Server:Docker 容器下的主从同步实现

华为云开发者联盟

数据库 后端 华为云 华为云开发者联盟 企业号 6 月 PK 榜

2023年6月墨天轮中国图数据库排行榜:TGS 开新局,创邻和字节多点突破露锋芒

墨天轮

数据库 图数据库 国产数据库 NoSQL 数据库

免费体验,有奖评测!低代码开发平台魔笔发布评测令

移动研发平台EMAS

开发者 低代码开发 有奖评测 快速开发全端应用

垂域LLM应用实践

csunny

大模型 GPT LLM

当 MQTT 遇上 ChatGPT:探索可自然交互的物联网智能应用

EMQ映云科技

物联网 mqtt ChatGPT

中航机载系统共性技术有限公司*IoTDB | 端边云架构预计节省百万存储成本,实现基于工业物联网的复杂机载制造系统协同

Apache IoTDB

物联网 端边云协同架构 IoTDB 中国航天

2023“科创中国”大湾区青年百人会论坛即将召开

飞桨PaddlePaddle

人工智能 百度 paddle 飞桨

2023年,中小企业的发展新风向

互联网工科生

低代码 企业 数字化

共建智能汽车数据管理方案 | 4.15 IoTDB X EMQ 主题 Meetup 回顾

Apache IoTDB

智能汽车 emq IoTDB

共建智慧工厂物联网平台方案 | 6.10 IoTDB X EMQ 主题 Meetup 回顾

Apache IoTDB

物联网 emq IoTDB

自我管理型团队:企业组织力提升利器

敏捷开发

项目管理 敏捷开发 高效协作 自我管理型团队

陶哲轩甩出调教GPT-4聊天记录,点击领取大佬的研究助理

Openlab_cosmoplat

开源社区 GPT

个推文案圈人模型助力TT语音智选人群,实现消息推送点击率提升120%

个推

消息推送 移动开发

基于k6和python进行自动化性能测试

华为云开发者联盟

前端 华为云 华为云开发者联盟 企业号 6 月 PK 榜

助力智能制造数字化转型 | 5.31 IoTDB & 中航机载制造行业客户分享会回顾

Apache IoTDB

智能制造 IoTDB 中国航天

谁与争锋!手机直播源码知识分享之主播PK功能

山东布谷科技

软件开发 源码搭建 手机直播源码 手机直播

国内首发|性能飙升100% 焱融全闪存储成功适配 InfiniBand 400Gbps 网络

焱融科技

#分布式文件存储 #文件存储 #全闪存储 #高性能存储

pnpm才是前端工程化项目的未来

互联网工科生

前端 npm 工程化

华为云联合万木健康打造医疗医学科普和患者教育数字人引擎

华为云开发者联盟

人工智能 华为云 数字人 华为云开发者联盟 企业号 6 月 PK 榜

突破界限,共创未来!MIAOYUN“一云多芯”全栈信创解决方案获认可!

MIAOYUN

信创 一云多芯解决方案 信创云 信创生态 一云多芯

Apache IoTDB 荣获国家网信办 2022 年中国开源创新大赛决赛一等奖,三位核心研发荣获表彰!

Apache IoTDB

开源数据库 IoTDB

MySQL 如何快速插入大量测试数据

hungxy

MySQL 后端

突破边界:高性能计算引领LLM驶向通用人工智能AGI的创新纪元

GPU算力

AI时代风暴:低代码开发平台引领未来革命

不在线第一只蜗牛

人工智能 AI 低代码 数字化

社区新手小伙伴测评 | 使用 ChatGPT 可以帮助完成 IoTDB 部署吗?

Apache IoTDB

IoTDB ChatGPT

LED显示屏的种类和技术

Dylan

技术 LED显示屏 户外LED显示屏

理解 G1 GC 日志

摸鱼编程

JVM G1GC 可视化分析

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