SQL Postgres 外键“更新时”和“删除时”选项如何工作？

How do the Postgres foreign key 'on update' and 'on delete' options work?

提问by meleyal

Can anyone provide a clear explanation / example of what these functions do, and when it's appropriate to use them?

任何人都可以提供这些功能的作用以及何时适合使用它们的清晰解释/示例吗？

回答by matt b

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:

Disallow deleting a referenced product

Delete the orders as well

Something else?

我们知道外键不允许创建与任何产品无关的订单。但是，如果在创建引用它的订单后删除了产品怎么办？SQL 也允许您处理它。直观地说，我们有几个选择：

不允许删除引用的产品

也删除订单

还有什么？

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.

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.

限制和级联删除是两个最常见的选项。RESTRICT 防止删除引用的行。NO ACTION 意味着如果检查约束时仍然存在任何引用行，则会引发错误；如果您未指定任何内容，则这是默认行为。（这两个选择之间的本质区别是 NO ACTION 允许将检查推迟到事务的后期，而 RESTRICT 不允许。） CASCADE 指定当删除引用的行时，应自动删除引用它的行以及。还有另外两个选项：SET NULL 和 SET DEFAULT。当引用的行被删除时，这些会导致引用列分别设置为空值或默认值。请注意，这些并不能免除您遵守任何约束条件。例如，

与 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 中使用级联？. 问题/答案背后的概念是相同的。

回答by Patrick Desjardins

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 次删除操作，并且具有 ON 删除功能的 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 执行相同操作。如果您更新表并且该字段有一个带有 On Update 的 FK，如果对 FK 进行了更改，您将在 FK 表上注意到。

回答by Patrick Desjardins

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.

道克说的是真的……倒是挺方便的。另一方面，让事情在数据库中自动发生可能是一个真正的问题，尤其是在消除数据时。将来可能有人会指望 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.

这与我设计数据库的程度完全无关。它与触发器引入的操作问题有关。

回答by Jeff Edwards

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)

与其编写方法来完成级联删除或级联更新的所有工作，您还可以简单地编写一条警告消息。比重新发明轮子容易得多，而且它让客户（以及新开发人员拿起代码）一目了然