postgresql RESTRICT 和 NO ACTION 之间的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14921668/
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
Difference between RESTRICT and NO ACTION
提问by Jeriho
From postgresql documentation:
RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.)
RESTRICT 防止删除引用的行。NO ACTION 意味着如果检查约束时仍然存在任何引用行，则会引发错误；如果您未指定任何内容，则这是默认行为。（这两个选择之间的本质区别在于 NO ACTION 允许将检查推迟到事务的后期，而 RESTRICT 则不允许。）
Lets check it. Create parent and child table:
让我们检查一下。创建父子表：
CREATE TABLE parent (
id serial not null,
CONSTRAINT parent_pkey PRIMARY KEY (id)
);
CREATE TABLE child (
id serial not null,
parent_id serial not null,
CONSTRAINT child_pkey PRIMARY KEY (id),
CONSTRAINT parent_fk FOREIGN KEY (parent_id)
REFERENCES parent (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
Populate some data:
填充一些数据：
insert into parent values(1);
insert into child values(5, 1);
And test does check is really deffered:
并且测试确实检查确实推迟了：
BEGIN;
delete from parent where id = 1; -- violates foreign key constraint, execution fails
delete from child where parent_id = 1;
COMMIT;
After first delete integrity was broken, but after second it would be restored. However, execution fails on first delete.
第一次删除完整性被破坏后，但第二次之后它会恢复。但是，第一次删除时执行失败。
Same for update:
更新相同：
BEGIN;
update parent set id = 2 where id = 1; -- same as above
update child set parent_id = 2 where parent_id = 1;
COMMIT;
In case of deletes I can swap statements to make it work, but in case of updates I just can't do them (it is achivable via deleting both rows and inserting new versions).
在删除的情况下，我可以交换语句以使其工作，但在更新的情况下我不能这样做（可以通过删除两行并插入新版本来实现）。
Many databases don't make any difference between RESTRICT and NO ACTION while postgres pretends to do otherwise. Is it (still) true?
许多数据库在 RESTRICT 和 NO ACTION 之间没有任何区别，而 postgres 假装不这样做。它（仍然）是真的吗？
采纳答案by Craig Ringer
The difference only arises when you define a constraint as DEFERRABLEwith an INITIALLY DEFERREDor INITIALLY IMMEDIATEmode.
只有在DEFERRABLE使用INITIALLY DEFERREDorINITIALLY IMMEDIATE模式定义约束时才会出现差异。
See SET CONSTRAINTS.
