postgresql 我应该暂时禁用外键约束吗？如何？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15006669/
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
Should I temporarily disable foreign key constraints? How?
提问by clapas
I have two tables:
我有两个表：
person:
id serial primary key,
name varchar(64) not null
task:
tenant_id integer not null references person (id) on delete cascade,
customer_id integer not null references person (id) on delete restrict
(They have a lot more columns than that, but the rest aren't relevant to the question.)
（他们有更多的列，但其余的与问题无关。）
The problem is, I want to cascade-delete a taskwhen its tenant personis deleted. But when the tenant and the customer are the same person, the customer_idforeign key constraint will restrict deletion.
问题是，我想在删除task租户时级联删除 a person。但是当租户和客户是同一个人时，customer_id外键约束会限制删除。
My question has two parts:
我的问题有两个部分：
- Is temporarily disabling the second foreign key my only option?
- If so, then how do I do that in PostgreSQL?
- 暂时禁用第二个外键是我唯一的选择吗？
- 如果是这样，那么我如何在 PostgreSQL 中做到这一点？
回答by Erwin Brandstetter
Effectively you create a race conditionwith contradicting rules.
您可以有效地创建具有相互矛盾规则的竞争条件。
My first impulse was to check whether a DEFERREDconstraint would help. But it makes sense that it doesn't make any difference.
我的第一个冲动是检查DEFERRED约束是否有帮助。但它没有任何区别是有道理的。
I found that the FK constraint that comes first in the CREATE TABLEscript is the winner of this race. If the ON DELETE CASCADEcomes first, the delete is cascaded, if ON DELETE RESTRICTcomes first, the operation is aborted.
我发现脚本中首先出现CREATE TABLE的 FK 约束是这场比赛的赢家。如果ON DELETE CASCADE在先，则删除级联，如果ON DELETE RESTRICT在先，则中止操作。
Consider the demo on SQL Fiddle.
考虑SQL Fiddle上的演示。
This seems to correlate with a smaller oidin the catalog table pg_constraint:
这似乎与oid目录表中的较小相关pg_constraint：
SELECT oid, * FROM pg_constraint WHERE conrelid = 'task'::regclass
But your feedback indicates, this is not the cause. Maybe pg_attribute.attnumdecides the race. Either way, as long as it is not documented behavior you cannot rely on it to stay that way in the next major version. Might be worth to post a question on [email protected].
但是您的反馈表明，这不是原因。也许pg_attribute.attnum决定比赛。无论哪种方式，只要它没有记录在案的行为，您就不能依赖它在下一个主要版本中保持这种状态。可能值得在 [email protected] 上发布问题。
Independent from all that, you need to consider other rows: even if CASCADEwould go through for a row in taskthat has both tenant_idand customer_idpointing to a person, it will still be restricted if any row has only customer_idreferencing person.
Another SQL Fiddledemonstrating the case.
与所有这些无关，您需要考虑其他行：即使CASCADE会通过task其中同时具有tenant_id和customer_id指向a 的行，person如果任何行只有customer_id引用，它仍然会受到限制person。
另一个SQL Fiddle演示了这个案例。
How to disable the constraint?
如何禁用约束？
Your best bet is to drop and recreate it. Do it all inside a transaction to make sure you don't corrupt referential integrity.
最好的办法是删除并重新创建它。在事务中执行所有操作，以确保不会破坏参照完整性。
BEGIN;
ALTER TABLE task DROP CONSTRAINT task_customer_id_fkey;
DELETE FROM person WHERE id = 3;
ALTER TABLE task ADD CONSTRAINT task_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES person (id) ON DELETE RESTRICT;
COMMIT;
This locks the table exclusively and is not fit for routine use in a multi-user environment.
这会以独占方式锁定表，不适合在多用户环境中日常使用。
How did I know the nameof the constraint? I took it from pg_constraintas demonstrated above. Might be easier to use an explicit constraint name to begin with:
我怎么知道约束的名称？我从pg_constraint上面演示了它。使用显式约束名称开头可能更容易：
CREATE TEMP TABLE task (
customer_id integer NOT NULL
,tenant_id integer NOT NULL REFERENCES person (id) ON DELETE CASCADE
,CONSTRAINT task_customer_id_fkey FOREIGN KEY (customer_id)
REFERENCES person (id) ON DELETE RESTRICT
);
There is also
还有
ALTER TABLE task DISABLE trigger ALL;
More in the manual here. But that would disable alltriggers. I had no luck trying to disable only the trigger created by the system to implement a single FK constraint.
此处的手册中有更多内容。但这会禁用所有触发器。我没有尝试仅禁用系统创建的触发器来实现单个 FK 约束。
Other alternatives would be to implement your regime with triggersor rules. That would work just fine, but those are not enforced as strictly as foreign keys.
