我经常使用ON DELETE CASCADE，但我从不使用ON UPDATE CASCADE，因为我不确定在什么情况下它会有用。
为了便于讨论，让我们看一些代码。
CREATE TABLE parent (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);
CREATE TABLE child (
id INT NOT NULL AUTO_INCREMENT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
);
对于ON DELETE CASCADE，如果删除带有id的父级，则child中的记录parent_id = parent。Id将被自动删除。这应该没有问题。
This means that ON UPDATE CASCADE will do the same thing when id of the parent is updated?
If (1) is true, it means that there is no need to use ON UPDATE CASCADE if parent.id is not updatable (or will never be updated) like when it is AUTO_INCREMENT or always set to be TIMESTAMP. Is that right?
If (2) is not true, in what other kind of situation should we use ON UPDATE CASCADE?
What if I (for some reason) update the child.parent_id to be something not existing, will it then be automatically deleted?
好吧，我知道，上面的一些问题可以通过编程测试来理解，但我也想知道这其中是否有依赖于数据库供应商的。
请照点光。
ON UPDATE和ON DELETE指定当父表中的一行被更新和删除时将执行哪个操作。允许的操作包括:NO ACTION、CASCADE、SET NULL和SET DEFAULT。
删除父表中的行操作
如果您删除父表中的一行或多行，您可以设置以下操作之一:
ON DELETE NO ACTION: SQL Server raises an error and rolls back the delete action on the row in the parent table.
ON DELETE CASCADE: SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table.
ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must be nullable.
ON DELETE SET DEFAULT: SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified.
By default, SQL Server appliesON DELETE NO ACTION if you don’t explicitly specify any action.
父表中行更新操作
如果您更新父表中的一行或多行，您可以设置以下操作之一:
ON UPDATE NO ACTION: SQL Server raises an error and rolls back the update action on the row in the parent table.
ON UPDATE CASCADE: SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated.
ON UPDATE SET NULL: SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated. Note that the foreign key columns must be nullable for this action to execute.
ON UPDATE SET DEFAULT: SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated.
FOREIGN KEY (foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE <action>
ON DELETE <action>;
请参阅参考教程。
ON UPDATE和ON DELETE指定当父表中的一行被更新和删除时将执行哪个操作。允许的操作包括:NO ACTION、CASCADE、SET NULL和SET DEFAULT。
删除父表中的行操作
如果您删除父表中的一行或多行，您可以设置以下操作之一:
ON DELETE NO ACTION: SQL Server raises an error and rolls back the delete action on the row in the parent table.
ON DELETE CASCADE: SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table.
ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must be nullable.
ON DELETE SET DEFAULT: SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified.
By default, SQL Server appliesON DELETE NO ACTION if you don’t explicitly specify any action.
父表中行更新操作
如果您更新父表中的一行或多行，您可以设置以下操作之一:
ON UPDATE NO ACTION: SQL Server raises an error and rolls back the update action on the row in the parent table.
ON UPDATE CASCADE: SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated.
ON UPDATE SET NULL: SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated. Note that the foreign key columns must be nullable for this action to execute.
ON UPDATE SET DEFAULT: SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated.
FOREIGN KEY (foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE <action>
ON DELETE <action>;
请参阅参考教程。
这是一个很好的问题，我昨天也有同样的问题。我考虑过这个问题，特别是搜索是否存在类似“ON UPDATE CASCADE”的东西，幸运的是SQL的设计师也考虑过这个问题。我同意泰德的观点。施特劳斯，我还评论了诺兰的案子。
When did I use it? Like Ted pointed out, when you are treating several databases at one time, and the modification in one of them, in one table, has any kind of reproduction in what Ted calls "satellite database", can't be kept with the very original ID, and for any reason you have to create a new one, in case you can't update the data on the old one (for example due to permissions, or in case you are searching for fastness in a case that is so ephemeral that doesn't deserve the absolute and utter respect for the total rules of normalization, simply because will be a very short-lived utility)
因此，我同意两点:
(a)是的，在很多时候，一个更好的设计可以避免这种情况;但
(b)在迁移、复制数据库或解决紧急情况的情况下，它是一个很好的工具，幸运的是，当我去搜索它是否存在时，它就在那里。
几天前，我遇到了一个触发器问题，我发现ON UPDATE CASCADE可以很有用。看一下这个例子(PostgreSQL):
CREATE TABLE club
(
key SERIAL PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE band
(
key SERIAL PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE concert
(
key SERIAL PRIMARY KEY,
club_name TEXT REFERENCES club(name) ON UPDATE CASCADE,
band_name TEXT REFERENCES band(name) ON UPDATE CASCADE,
concert_date DATE
);
在我的问题中，我必须定义一些额外的操作(触发器)来更新音乐会的表。这些操作需要修改“club_name”和“band_name”。因为参考问题，我没能做这件事。我不能修改音乐会，然后处理俱乐部和乐队的桌子。我不能用另一种方法来做。ON UPDATE级联是解决这个问题的关键。
