限制与不采取行动之间的区别

从postgresql文档:

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.)

限制防止删除引用的行。没有操作意味着如果在检查约束时仍然存在引用行，则会引发错误;如果您没有指定任何内容，这就是默认行为。(这两个选项的本质区别在于，没有操作允许将检查延迟到事务的稍后时间，而limit则不允许。)

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?

许多数据库在“限制”和“不操作”之间没有任何区别，而postgres假装没有区别。(仍然)是真的吗?

1 个解决方案

#1

The difference only arises when you define a constraint as DEFERRABLE with an INITIALLY DEFERRED or INITIALLY IMMEDIATE mode.

当您定义一个可延迟的约束和最初延迟的或最初的直接模式时，差异才会出现。

See SET CONSTRAINTS.

看到设置约束。

#1