如何在 postgresql 中禁用外键约束 [英] How to disable foreign key constraints in 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 CONSTRAINT
s
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 CONSTRAINT
s
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屋!