Can anyone provide a clear explanation / example of what these functions do, and when it's appropriate to use them?
任何人都可以提供这些功能的明确解释/示例，以及何时使用它们？
4 个解决方案
#1
24
Straight from the manual...
直接从手册......
We know that the foreign keys disallow creation of orders that do not relate to any products. But what if a product is removed after an order is created that references it? SQL allows you to handle that as well. Intuitively, we have a few options:
我们知道外键不允许创建与任何产品无关的订单。但是，如果在创建引用它的订单后删除了产品，该怎么办？ SQL允许您处理它。直观地说，我们有几个选择：
Disallow deleting a referenced product
禁止删除引用的产品
Delete the orders as well
也删除订单
Something else?
别的什么？
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
Restricting and cascading deletes are the two most common options. 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.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing columns to be set to nulls or default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key, the operation will fail.
限制和级联删除是两种最常见的选项。 RESTRICT可防止删除引用的行。 NO ACTION表示如果在检查约束时仍存在任何引用行，则会引发错误;如果您未指定任何内容，这是默认行为。 （这两个选项之间的本质区别在于NO ACTION允许将检查推迟到事务的后期，而RESTRICT则不会。）CASCADE指定当删除引用的行时，应自动删除引用它的行同样。还有另外两个选项：SET NULL和SET DEFAULT。这些导致引用列分别在删除引用行时设置为空值或默认值。请注意，这些不能免除您观察任何约束。例如，如果操作指定SET DEFAULT但默认值不满足外键，则操作将失败。
Analogous to ON DELETE there is also ON UPDATE which is invoked when a referenced column is changed (updated). The possible actions are the same.
类似于ON DELETE，还有ON UPDATE，当引用的列被更改（更新）时调用。可能的行动是一样的。
edit: You might want to take a look at this related question: When/Why to use Cascading in SQL Server?. The concepts behind the question/answers are the same.
编辑：您可能想看看这个相关的问题：何时/为什么在SQL Server中使用级联？问题/答案背后的概念是相同的。
#2
0
I have a PostGreSQL database and I use On Delete when I have a user that I delete from the database and I need to delete it's information from other table. This ways I need to do only 1 delete and FK that has ON delete will delete information from other table.
我有一个PostGreSQL数据库，当我有一个用户从数据库中删除时，我使用On Delete，我需要从其他表中删除它的信息。这种方式我只需要做1个删除和有删除的FK将删除其他表中的信息。
You can do the same with ON Update. If you update the table and the field have a FK with On Update, if a change is made on the FK you will be noticed on the FK table.
您可以使用ON Update执行相同的操作。如果您更新表并且该字段的FK为On Update，如果对FK进行了更改，您将在FK表上注意到。
#3
0
What Daok says is true... it can be rather convenient. On the other hand, having things happen automagically in the database can be a real problem, especially when it comes to eliminating data. It's possible that in the future someone will count on the fact that FKs usually prevent deletion of parents when there are children and not realize that your use of On Delete Cascade not only doesn't prevent deletion, it makes huge amounts of data in dozens of other tables go away thanks to a waterfall of cascading deletes.
Daok说的是真的......它可以相当方便。另一方面，在数据库中自动发生事情可能是一个真正的问题，特别是在消除数据时。有可能在未来有人会指望FK通常会在有孩子的时候阻止删除父母并且没有意识到你使用On Delete Cascade不仅不会阻止删除这一事实，它会在数十个中产生大量数据由于瀑布的级联删除，其他表格消失了。
@Arthur's comment.
@Arthur的评论。
The more frequently "hidden" things happen in the database the less likely it becomes that anyone will ever have a good handle on what is going on. Triggers (and this is essentially a trigger) can cause my simple action of deleting a row, to have wide ranging consequences throughout my database. I issue a Delete statement and 17 tables are affected with cascades of triggers and constraints and none of this is immediately apparent to the issuer of the command. OTOH, If I place the deletion of the parent and all its children in a procedure then it is very easy and clear for anyone to see EXACTLY what is going to happen when I issue the command.
数据库中发生的“隐藏”事件越频繁，就越不可能有人能够很好地处理正在发生的事情。触发器（这实际上是一个触发器）可以导致我删除行的简单操作，从而在整个数据库中产生广泛的后果。我发出一个Delete语句，17个表受到触发器和约束级联的影响，对于命令的发布者来说，这些都不会立即显现出来。 OTOH，如果我将父母及其所有孩子的删除放在一个程序中，那么任何人都可以非常容易地清楚地看到我发出命令时会发生什么。
It has absolutely nothing to do with how well I design a database. It has everything to do with the operational issues introduced by triggers.
它与我设计数据库的方式完全无关。它与触发器引入的操作问题有关。
#4
0
Instead of writing the method to do all the work, of the cascade delete or cascade update, you could simply write a warning message instead. A lot easier than reinventing the wheel, and it makes it clear to the client (and new developers picking up the code)
您可以简单地编写警告消息，而不是编写方法来执行级联删除或级联更新的所有工作。比重新发明*容易得多，它让客户（以及新开发人员获取代码）变得清晰
#1
24
Straight from the manual...
直接从手册......
We know that the foreign keys disallow creation of orders that do not relate to any products. But what if a product is removed after an order is created that references it? SQL allows you to handle that as well. Intuitively, we have a few options:
我们知道外键不允许创建与任何产品无关的订单。但是，如果在创建引用它的订单后删除了产品，该怎么办？ SQL允许您处理它。直观地说，我们有几个选择：
Disallow deleting a referenced product
禁止删除引用的产品
Delete the orders as well
也删除订单
Something else?
别的什么？
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
Restricting and cascading deletes are the two most common options. 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.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing columns to be set to nulls or default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key, the operation will fail.
限制和级联删除是两种最常见的选项。 RESTRICT可防止删除引用的行。 NO ACTION表示如果在检查约束时仍存在任何引用行，则会引发错误;如果您未指定任何内容，这是默认行为。 （这两个选项之间的本质区别在于NO ACTION允许将检查推迟到事务的后期，而RESTRICT则不会。）CASCADE指定当删除引用的行时，应自动删除引用它的行同样。还有另外两个选项：SET NULL和SET DEFAULT。这些导致引用列分别在删除引用行时设置为空值或默认值。请注意，这些不能免除您观察任何约束。例如，如果操作指定SET DEFAULT但默认值不满足外键，则操作将失败。
Analogous to ON DELETE there is also ON UPDATE which is invoked when a referenced column is changed (updated). The possible actions are the same.
类似于ON DELETE，还有ON UPDATE，当引用的列被更改（更新）时调用。可能的行动是一样的。
edit: You might want to take a look at this related question: When/Why to use Cascading in SQL Server?. The concepts behind the question/answers are the same.
编辑：您可能想看看这个相关的问题：何时/为什么在SQL Server中使用级联？问题/答案背后的概念是相同的。
#2
0
I have a PostGreSQL database and I use On Delete when I have a user that I delete from the database and I need to delete it's information from other table. This ways I need to do only 1 delete and FK that has ON delete will delete information from other table.
我有一个PostGreSQL数据库，当我有一个用户从数据库中删除时，我使用On Delete，我需要从其他表中删除它的信息。这种方式我只需要做1个删除和有删除的FK将删除其他表中的信息。
You can do the same with ON Update. If you update the table and the field have a FK with On Update, if a change is made on the FK you will be noticed on the FK table.
您可以使用ON Update执行相同的操作。如果您更新表并且该字段的FK为On Update，如果对FK进行了更改，您将在FK表上注意到。
#3
0
What Daok says is true... it can be rather convenient. On the other hand, having things happen automagically in the database can be a real problem, especially when it comes to eliminating data. It's possible that in the future someone will count on the fact that FKs usually prevent deletion of parents when there are children and not realize that your use of On Delete Cascade not only doesn't prevent deletion, it makes huge amounts of data in dozens of other tables go away thanks to a waterfall of cascading deletes.
Daok说的是真的......它可以相当方便。另一方面，在数据库中自动发生事情可能是一个真正的问题，特别是在消除数据时。有可能在未来有人会指望FK通常会在有孩子的时候阻止删除父母并且没有意识到你使用On Delete Cascade不仅不会阻止删除这一事实，它会在数十个中产生大量数据由于瀑布的级联删除，其他表格消失了。
@Arthur's comment.
@Arthur的评论。
The more frequently "hidden" things happen in the database the less likely it becomes that anyone will ever have a good handle on what is going on. Triggers (and this is essentially a trigger) can cause my simple action of deleting a row, to have wide ranging consequences throughout my database. I issue a Delete statement and 17 tables are affected with cascades of triggers and constraints and none of this is immediately apparent to the issuer of the command. OTOH, If I place the deletion of the parent and all its children in a procedure then it is very easy and clear for anyone to see EXACTLY what is going to happen when I issue the command.
数据库中发生的“隐藏”事件越频繁，就越不可能有人能够很好地处理正在发生的事情。触发器（这实际上是一个触发器）可以导致我删除行的简单操作，从而在整个数据库中产生广泛的后果。我发出一个Delete语句，17个表受到触发器和约束级联的影响，对于命令的发布者来说，这些都不会立即显现出来。 OTOH，如果我将父母及其所有孩子的删除放在一个程序中，那么任何人都可以非常容易地清楚地看到我发出命令时会发生什么。
It has absolutely nothing to do with how well I design a database. It has everything to do with the operational issues introduced by triggers.
它与我设计数据库的方式完全无关。它与触发器引入的操作问题有关。
#4
0
Instead of writing the method to do all the work, of the cascade delete or cascade update, you could simply write a warning message instead. A lot easier than reinventing the wheel, and it makes it clear to the client (and new developers picking up the code)
您可以简单地编写警告消息，而不是编写方法来执行级联删除或级联更新的所有工作。比重新发明*容易得多，它让客户（以及新开发人员获取代码）变得清晰