级联删除

外键约束删除有5种选项：

🌐 There are 5 options for foreign key constraint deletes:

CASCADE： 当父表中的一行被删除时，子表中所有相关的行也会被删除。
限制: 如果父表中有行被删除，但子表中存在相关的行，则删除操作会被中止。
设置为 NULL: 当父表中的一行被删除时，子表中的外键列的值会被设置为 NULL。
设置默认值: 当父表中的一行被删除时，子表中外键列的值会被设为它们的默认值。
无操作： 这个选项类似于 RESTRICT，但它也可以选择“延迟”到事务结束。这意味着其他级联删除可以先执行，然后这个删除约束只有在事务结束时仍有引用数据存在时才会抛出错误。

在使用 "ON DELETE" 子句定义外键约束时，可以指定这些选项。例如，下面的 SQL 语句创建了一个带有 CASCADE 选项的外键约束：

🌐 These options can be specified when defining a foreign key constraint using the "ON DELETE" clause. For example, the following SQL statement creates a foreign key constraint with the CASCADE option:

1
alter table child_table
2
add constraint fk_parent foreign key (parent_id) references parent_table (id)
3
  on delete cascade;

这意味着当从 parent_table 删除一行时，所有在 child_table 中相关的行也会被删除。

🌐 This means that when a row is deleted from the parent_table, all related rows in the child_table will be deleted as well.

`RESTRICT` 对比 `NO ACTION`#

🌐 RESTRICT vs NO ACTION

NO ACTION和RESTRICT之间的区别很微妙，有点容易让人混淆。

🌐 The difference between NO ACTION and RESTRICT is subtle and can be a bit confusing.

NO ACTION 和 RESTRICT 都用于防止在父表中删除某行时，如果子表中有相关行会被删除。不过，它们的行为有一些微妙的差别。

🌐 Both NO ACTION and RESTRICT are used to prevent deletion of a row in a parent table if there are related rows in a child table. However, there is a subtle difference in how they behave.

当外键约束使用选项 RESTRICT 定义时，这意味着如果父表中的某行被删除，数据库会立即报错并阻止删除父表中的该行。数据库不会删除、更新或将关联表中的任何行设为 NULL。

🌐 When a foreign key constraint is defined with the option RESTRICT, it means that if a row in the parent table is deleted, the database will immediately raise an error and prevent the deletion of the row in the parent table. The database will not delete, update or set to NULL any rows in the referenced tables.

当外键约束使用 NO ACTION 选项定义时，这意味着如果父表中的一行被删除，数据库也会报错并阻止删除父表中的该行。不过和 RESTRICT 不同，NO ACTION 可以使用 INITIALLY DEFERRED 延迟检查。只有在事务结束时引用的行仍然存在时，才会触发上述错误。

🌐 When a foreign key constraint is defined with the option NO ACTION, it means that if a row in the parent table is deleted, the database will also raise an error and prevent the deletion of the row in the parent table. However unlike RESTRICT, NO ACTION has the option to defer the check using INITIALLY DEFERRED. This will only raise the above error if the referenced rows still exist at the end of the transaction.

RESTRICT 的不同之处在于，被标记为 NO ACTION INITIALLY DEFERRED 的约束会被延迟到事务结束时才执行，而不是立即运行。例如，如果同一张表之间还有另一个被标记为 CASCADE 的外键约束，级联操作会先执行并删除被引用的行，而延迟约束不会报错。否则，如果到事务结束时仍有行引用父行，就会像以前一样报错。跟 RESTRICT 一样，数据库不会删除、更新或将被引用表中的任何行设为 NULL。

🌐 The difference from RESTRICT is that a constraint marked as NO ACTION INITIALLY DEFERRED is deferred until the end of the transaction, rather than running immediately. If, for example there is another foreign key constraint between the same tables marked as CASCADE, the cascade will occur first and delete the referenced rows, and no error will be thrown by the deferred constraint. Otherwise if there are still rows referencing the parent row by the end of the transaction, an error will be raised as before. Like RESTRICT, the database will not delete, update or set to NULL any rows in the referenced tables.

实际上，你可以根据需要使用 NO ACTION 或 RESTRICT。如果你没有指定任何选项，默认行为是 NO ACTION。如果你想把检查延迟到事务结束时再进行，可以使用 NO ACTION INITIALLY DEFERRED。

🌐 In practice, you can use either NO ACTION or RESTRICT depending on your needs. NO ACTION is the default behavior if you do not specify anything. If you prefer to defer the check until the end of the transaction, use NO ACTION INITIALLY DEFERRED.

示例 #

🌐 Example

为了说明区别，举个例子。我们将使用以下数据：

🌐 To illustrate the difference, use the following example. We'll use the following data:

grandparent

id	名字
1	伊丽莎白

parent

id	名字	`parent_id`
1	查尔斯	1
2	戴安娜	1

child

id	名字	父亲	母亲
1	威廉	1	2

要创建这些表格及其数据，我们运行：

🌐 To create these tables and their data, we run:

1
create table grandparent (
2
  id serial primary key,
3
  name text
4
);
5
6
create table parent (
7
  id serial primary key,
8
  name text,
9
  parent_id integer references grandparent (id)
10
    on delete cascade
11
);
12
13
create table child (
14
  id serial primary key,
15
  name text,
16
  father integer references parent (id)
17
    on delete restrict
18
);
19
20
insert into grandparent
21
  (id, name)
22
values
23
  (1, 'Elizabeth');
24
25
insert into parent
26
  (id, name, parent_id)
27
values
28
  (1, 'Charles', 1);
29
30
insert into parent
31
  (id, name, parent_id)
32
values
33
  (2, 'Diana', 1);
34
35
-- We'll just link the father for now
36
insert into child
37
  (id, name, father)
38
values
39
  (1, 'William', 1);

`RESTRICT`#

RESTRICT 会阻止删除并抛出一个错误：

1
postgres=# delete from grandparent;
2
ERROR: update or delete on table "parent" violates foreign key constraint "child_father_fkey" on table "child"
3
DETAIL: Key (id)=(1) is still referenced from table "child".

尽管父级和祖父级之间的外键约束是 CASCADE，但子级和父级之间的约束是 RESTRICT。因此会报错，并且没有记录被删除。

🌐 Even though the foreign key constraint between parent and grandparent is CASCADE, the constraint between child and father is RESTRICT. Therefore an error is raised and no records are deleted.

`NO ACTION`#

把子女和父亲的关系改为 NO ACTION：

🌐 Change the child-father relationship to NO ACTION:

1
alter table child
2
drop constraint child_father_fkey;
3
4
alter table child
5
add constraint child_father_fkey foreign key (father) references parent (id)
6
  on delete no action;

我们看到 NO ACTION 也会阻止删除并抛出一个错误：

🌐 We see that NO ACTION will also prevent a delete and raise an error:

1
postgres=# delete from grandparent;
2
ERROR: update or delete on table "parent" violates foreign key constraint "child_father_fkey" on table "child"
3
DETAIL: Key (id)=(1) is still referenced from table "child".

`NO ACTION INITIALLY DEFERRED`#

我们会把子表和父表之间的外键约束改为 NO ACTION INITIALLY DEFERRED：

🌐 We'll change the foreign key constraint between child and father to be NO ACTION INITIALLY DEFERRED:

1
alter table child
2
drop constraint child_father_fkey;
3
4
alter table child
5
add constraint child_father_fkey foreign key (father) references parent (id)
6
  on delete no action initially deferred;

在这里你会看到 INITIALLY DEFFERED 看起来像 NO ACTION 或 RESTRICT 一样运行。当我们执行删除操作时，好像没有任何区别：

🌐 Here you will see that INITIALLY DEFFERED seems to operate like NO ACTION or RESTRICT. When we run a delete, it seems to make no difference:

1
postgres=# delete from grandparent;
2
ERROR: update or delete on table "parent" violates foreign key constraint "child_father_fkey" on table "child"
3
DETAIL: Key (id)=(1) is still referenced from table "child".

但是，当我们把它和其他约束结合起来时，其他约束就会优先。例如，运行相同的场景，但添加一个 mother 列，并有一个 CASCADE 删除：

🌐 But, when we combine it with other constraints, then any other constraints take precedence. For example, run the same scenario but add a mother column that has a CASCADE delete:

1
alter table child
2
add column mother integer references parent (id)
3
  on delete cascade;
4
5
update child
6
set mother = 2
7
where id = 1;

然后在 grandparent 表上运行删除操作：

🌐 Then run a delete on the grandparent table:

1
postgres=# delete from grandparent;
2
DELETE 1
3
4
postgres=# select * from parent;
5
 id | name | parent_id
6
----+------+-----------
7
(0 rows)
8
9
postgres=# select * from child;
10
 id | name | father | mother
11
----+------+--------+--------
12
(0 rows)

mother 的删除优先于 father，所以 William 被删除了。William 被删除后，没有任何对“Charles”的引用，所以他可以被删除，即使之前没有 INITIALLY DEFERRED 的情况下他不能被删除。

🌐 The mother deletion took precedence over the father, and so William was deleted. After William was deleted, there was no reference to “Charles” and so he was free to be deleted, even though previously he wasn't (without INITIALLY DEFERRED).

RESTRICT 对比 NO ACTION#

示例 #

RESTRICT#

NO ACTION#

NO ACTION INITIALLY DEFERRED#

`RESTRICT` 对比 `NO ACTION`#

`RESTRICT`#

`NO ACTION`#

`NO ACTION INITIALLY DEFERRED`#