PostgreSQL - 禁用约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2679854/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - disabling constraints
提问by azp74
I have a table with approx 5 million rows which has a fk constraint referencing the primary key of another table (also approx 5 million rows).
我有一个大约有 500 万行的表，它有一个引用另一个表的主键的 fk 约束（也大约有 500 万行）。
I need to delete about 75000 rows from both tables. I know that if I try doing this with the fk constraint enabled it's going to take an unacceptable amount of time.
我需要从两个表中删除大约 75000 行。我知道如果我尝试在启用 fk 约束的情况下执行此操作，它将花费无法接受的时间。
Coming from an Oracle background my first thought was to disable the constraint, do the delete & then reenable the constraint. PostGres appears to let me disable constraint triggers if I am a super user (I'm not, but I am logging in as the user that owns/created the objects) but that doesn't seem to be quite what I want.
来自 Oracle 背景，我的第一个想法是禁用约束，执行删除，然后重新启用约束。如果我是超级用户（我不是，但我以拥有/创建对象的用户身份登录），PostGres 似乎让我禁用约束触发器，但这似乎不是我想要的。
The other option is to drop the constraint and then reinstate it. I'm worried that rebuilding the constraint is going to take ages given the size of my tables.
另一种选择是删除约束，然后恢复它。鉴于我的表的大小，我担心重建约束需要很长时间。
Any thoughts?
有什么想法吗？
edit: after Billy's encouragement I've tried doing the delete without changing any constraints and it takes in excess of 10 minutes. However, I have discovered that the table from which I'm trying to delete has a self referential foreign key ... duplicated (& non indexed).
编辑：在比利的鼓励下，我尝试在不改变任何约束的情况下进行删除，这需要超过 10 分钟。但是，我发现我试图从中删除的表有一个自引用外键......重复（& 非索引）。
Final update - I dropped the self referential foreign key, did my delete and added it back in. Billy's right all round but unfortunately I can't accept his comment as the answer!
最终更新 - 我删除了自引用外键，删除并重新添加了它。比利完全正确，但不幸的是我不能接受他的评论作为答案！
回答by Magnus Hagander
Per previous comments, it should be a problem. That said, there is a command that may be what you're looking to - it'll set the constraints to deferred so they're checked on COMMIT, not on every delete. If you're doing just one big DELETE of all the rows, it won't make a difference, but if you're doing it in pieces, it will.
根据之前的评论，这应该是一个问题。也就是说，有一个命令可能正是您想要的——它会将约束设置为延迟，以便在 COMMIT 时检查它们，而不是在每次删除时检查它们。如果您只对所有行进行一次大的 DELETE，它不会有什么不同，但如果您是分段进行的，它会有所不同。
SET CONSTRAINTS ALL DEFERRED
is what you are looking for in that case. Note that constraints must be marked as DEFERRABLEbefore they can be deferred. For example:
在这种情况下，您正在寻找的是什么。请注意，必须先将约束标记为DEFERRABLE可以延迟。例如：
ALTER TABLE table_name
ADD CONSTRAINT constraint_uk UNIQUE(column_1, column_2)
DEFERRABLE INITIALLY IMMEDIATE;
The constraint can then be deferred in a transaction or function as follows:
然后可以在事务或函数中延迟约束，如下所示：
CREATE OR REPLACE FUNCTION f() RETURNS void AS
$BODY$
BEGIN
SET CONSTRAINTS ALL DEFERRED;
-- Code that temporarily violates the constraint...
-- UPDATE table_name ...
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
回答by gersonZaragocin
What worked for me was to disable one by one the TRIGGERSof those tables that are gonna be involved in the DELETEoperation.
对我有用的是一张一张地禁用TRIGGERS将要参与DELETE操作的那些表。
ALTER TABLE reference DISABLE TRIGGER ALL;
DELETE FROM reference WHERE refered_id > 1;
ALTER TABLE reference ENABLE TRIGGER ALL;
Solution is working in version 9.3.16. In my case time went from 45 minutes to 14 seconds executing DELETEoperations.
解决方案适用于 9.3.16 版。在我的例子中，执行DELETE操作的时间从 45 分钟缩短到 14 秒。
As stated in the comments section by @amphetamachine, you will need to have adminprivileges to the tables to perform this task.
正如@amphetamachine 的评论部分所述，您需要拥有admin表的权限才能执行此任务。
回答by Jonathan Fuerth
If you try DISABLE TRIGGER ALLand get an error like permission denied: "RI_ConstraintTrigger_a_16428" is a system trigger(I got this on Amazon RDS), try this:
如果您尝试DISABLE TRIGGER ALL遇到类似permission denied: "RI_ConstraintTrigger_a_16428" is a system trigger（我在 Amazon RDS 上遇到过）的错误，请尝试以下操作：
set session_replication_role to replica;
If this succeeds, all triggers that underlie table constraints will be disabled. Now it's up to you to make sure your changes leave the DB in a consistent state!
如果此操作成功，将禁用作为表约束基础的所有触发器。现在由您来确保您的更改使数据库保持一致状态！
Then when you are done, reenable triggers & constraints for your session with:
然后，当您完成后，使用以下命令重新启用会话的触发器和约束：
set session_replication_role to default;
回答by Joe Lapp
(This answer assumes your intent is to delete all of the rows of these tables, not just a selection.)
（此答案假定您的意图是删除这些表的所有行，而不仅仅是选择。）
I also had to do this, but as part of a test suite. I found the answer, suggested elsewhere on SO. Use TRUNCATE TABLEas follows:
我也必须这样做，但作为测试套件的一部分。我找到了答案，建议在 SO 的其他地方。使用TRUNCATE TABLE如下：
TRUNCATE TABLE <list-of-table-names> [RESTART IDENTITY] [CASCADE];
The following quickly deletes all rows from tables table1, table2, and table3, provided that there are no references to rows of these tables from tables not listed:
如果没有从未列出的表中引用这些表的行，以下内容会快速删除表table1、table2和 中的所有行table3：
TRUNCATE TABLE table1, table2, table3;
As long as references are between the tables listed, PostgreSQL will delete all the rows without concern for referential integrity. If a table other than those listed references a row of one of these tables, the query will fail.
只要引用在列出的表之间，PostgreSQL 就会删除所有行而不考虑引用完整性。如果未列出的表引用这些表之一的行，则查询将失败。
However, you can qualify the query so that it also truncates all tables with references to the listed tables (although I have not tried this):
但是，您可以限定查询，以便它也截断所有引用所列表的表（尽管我还没有尝试过）：
TRUNCATE TABLE table1, table2, table3 CASCADE;
By default, the sequences of these tables do not restart numbering. New rows will continue with the next number of the sequence. To restart sequence numbering:
默认情况下，这些表的序列不会重新编号。新行将继续使用序列的下一个编号。要重新启动序列编号：
TRUNCATE TABLE table1, table2, table3 RESTART IDENTITY;
回答by Weekend Baf
My PostgreSQL is 9.6.8.
我的 PostgreSQL 是 9.6.8。
set session_replication_role to replica;
work for me but I need permission.
对我来说有效，但我需要许可。
I login psql with super user.
我用超级用户登录 psql。
sudo -u postgres psql
Then connect to my database
然后连接到我的数据库
\c myDB
And run:
并运行：
set session_replication_role to replica;
Now I can delete from table with constraint.
现在我可以从带有约束的表中删除。
回答by Alam Usmani
Disable all table constraints
禁用所有表约束
ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName
-- Enable all table constraints
-- 启用所有表约束
ALTER TABLE TableName CHECK CONSTRAINT ConstraintName
