如何在 postgresql 中禁用外键约束 [英] How to disable foreign key constraints in postgresql

查看:1141
本文介绍了如何在 postgresql 中禁用外键约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用 AWS Aurora Postgres 并使用 DMS 从 RDS postgres 迁移到 Aurora PG.为了执行 FULL LOAD,我想在所有对象上禁用外键约束和触发器.我可以禁用触发器,但找不到禁用约束的方法.

以下不起作用:

ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey;

它抛出:

<块引用>

错误:CONSTRAINT"处或附近的语法错误第 1 行:ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey;^SQL 状态:42601人物:30

在参数组中设置 "session_replication_role" = "replica" 不起作用.当 DMS 任务尝试截断准备的表部分时,它仍然因外键违规错误而失败.

请告知任何解决方法.

注意:我不能在下面做,因为在 RDS 中,即使使用主帐户我也无权这样做:

alter table so_items disable trigger ALL;

<块引用>

错误:权限被拒绝:RI_ConstraintTrigger_c_16520"是系统触发器SQL 状态:42501

解决方案

您不能禁用约束.这毫无意义.

您可以DROP CONSTRAINTs

ALTER TABLE so_items DROP CONSTRAINT so_items_so_id_fkey;

这将永久删除它,或将约束检查推迟到交易结束:

ALTER TABLE so_items ALTER CONSTRAINT so_items_so_id_fkey DEFERRABLE INITIALLY DEFERRED;

通过该修改,在当前事务结束时的修改之后评估约束.这将允许您打破事务内部的约束.

您不应修改 Postgres 约束所依赖的触发器.这是一个您不应该关心的实现细节.

编辑:也可以禁用触发器,这也会影响表的外键约束

ALTER TABLE so_items DISABLE TRIGGER ALL;

但是当您之后重新启用触发器时,不会检查外键.这可能会导致数据库中的外键无效/不一致.

I'm using AWS Aurora Postgres and using DMS to migrate from RDS postgres to Aurora PG. In order to perform the FULL LOAD I want to disable foreign key constraints and triggers on all the objects. I'm able to disable triggers but couldn't find a way to disable constraints.

Below doesn't work:

ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey;

It throws:

ERROR: syntax error at or near "CONSTRAINT" LINE 1: ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey; ^ SQL state: 42601 Character: 30

Setting "session_replication_role" = "replica" in the parameter group didn't work. While the DMS task tries to truncate the table part of preparation it still fails with foreign key violation errors.

Please advise any workarounds.

Note: I couldn't do below since in RDS I do not have permissions to do so even with master account:

alter table so_items disable trigger ALL;

ERROR: permission denied: "RI_ConstraintTrigger_c_16520" is a system trigger SQL state: 42501

解决方案

You cannot disable constraints. This would make no sense.

You may DROP CONSTRAINTs

ALTER TABLE so_items DROP CONSTRAINT so_items_so_id_fkey;

which will delete it permanently, or defer the constraint check to the end of transactions:

ALTER TABLE so_items ALTER CONSTRAINT so_items_so_id_fkey DEFERRABLE INITIALLY DEFERRED;

With that modification the constraint is evaluated after a modification at the end of the current transaction. This will allow you to break the constraint inside of an transaction.

You shouldn't modify the triggers a Postgres constraint relies on. This is an implementation detail for which you shouldn't care about.

Edit: It is also possible to disable the triggers which also affects the foreign key constraints of the table

ALTER TABLE so_items DISABLE TRIGGER ALL;

But when you are re-enabling the triggers afterwards, the foreign keys are not checked. This might lead to invalid / inconsistent foreign keys in the database.

这篇关于如何在 postgresql 中禁用外键约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆