外键约束：何时使用ON UPDATE和ON DELETE [英] Foreign key constraints: When to use ON UPDATE and ON DELETE
问题描述
我正在使用MySQL Workbench设计我的数据库模式，这是非常酷的，因为你可以做图表，并转换它们：P
使用InnoDB，因为它是Foreign Key支持。我注意到的一件事是，它允许你设置更新和删除外键的选项。有人可以解释Restrict，Cascade和设置null可以在一个简单的例子中使用吗？
例如，我有一个 user 表，其中包含 userID 。并且我有一个消息表 message 这是一个多对多的有2个外键（引用相同的主键， userID 在用户表中）。在此情况下，将更新时间和删除时间选项设置为有用吗？如果是这样，我应该选择哪一个？如果这不是一个很好的例子，你能提出一个很好的例子来说明这些是如何有用的吗？
谢谢
不要犹豫对数据库加入约束。你肯定有一个一致的数据库，这是使用数据库的好理由之一。特别是如果你有几个应用程序请求它（或只有一个应用程序，但使用不同的源直接模式和批处理模式）。
对于MySQL，你没有高级约束就像你在postgreSQL中一样，但至少外键约束是相当高级的。
我们举个例子，一个公司表有一个用户表，公司
CREATE TABLE COMPANY（
company_id INT NOT NULL，
company_name VARCHAR（50），
PRIMARY KEY（company_id）
）ENGINE = INNODB;
CREATE TABLE USER（
user_id INT，
user_name VARCHAR（50），
company_id INT，
INDEX company_id_idx（company_id），
FOREIGN KEY（company_id）REFERENCES COMPANY（company_id）ON ...
）ENGINE = INNODB;
让我们看看 ON UPDATE 子句：
- ON UPDATE RESTRICT ：默认：如果您尝试更新公司表中的company_id，如果一个USER至少在此公司链接，则拒绝操作。
- ON UPDATE NO ACTION ：与RESTRICT相同。
- ON UPDATE CASCADE ：通常是最好的一种：如果您更新公司表中某行的company_id，引擎将相应地更新引用此公司的所有USER行没有触发器在USER表上激活，警告）。
- ON UPDATE SET NULL ：如果您更新公司表中某行的company_id，则引擎将设置相关USER的company_id设置为NULL（应在USER company_id字段中提供）。在更新时我看不到任何有趣的事情，但我可能错了。
ON DELETE ： ： ：如果您尝试删除表COMPANY中的company_id ID，则如果一个USER至少链接到该公司，引擎将拒绝该操作，可以节省您的生命。
通常我的默认值为： ON DELETE RESTRICT ON UPDATE CASCADE 。与一些 ON DELETE CASCADE 对于轨道表（日志 - 不是所有日志，这样的东西）和 ON DELETE SET NULL 当主表是包含外键的表的简单属性时，例如USER表的JOB表。
/ strong>
这是我写了很久了。现在我想我应该添加一个重要的警告。 MySQL有一个大的文件限制与级联。 级联不触发触发。因此，如果您在该引擎中足够自信地使用触发器，您应该避免级联约束。
MySQL触发器仅针对SQL语句对表所做的更改激活。它们不会对视图中的更改激活，也不会通过对不向SQL Server传输SQL语句的API所做的更改来激活
触发器未被外键操作激活。
我不认为这一天会固定。外键约束由InnoDb存储管理，触发器由MySQL SQL引擎管理。两者是分开的。 Innodb是唯一一个具有约束管理的存储，也许他们可以直接在存储引擎中添加触发器，也许不是。
但是我有自己的意见，你应该在差的触发器实现和非常有用的外键约束支持之间选择。一旦你习惯了数据库的一致性，你会喜欢PostgreSQL。
I'm designing my database schema using MySQL Workbench, which is pretty cool because you can do diagrams and it converts them :P
Anyways, I've decided to use InnoDB because of it's Foreign Key support. One thing I noticed though is that it allows you to set On Update and on Delete options for foreign keys. Can someone explain where "Restrict", "Cascade" and set null could be used in a simple example?
For example, say I have a user table which includes a userID. And say I have a message table message which is a many-to-many which has 2 foreign keys (which reference the same primary key, userID in the user table). Is setting the On Update and On Delete options any useful in this case? If so, which one do I choose? If this isn't a good example, could you please come up with a good example to illustrate how these could be useful?
Thanks
Do not hesitate to put constraints on the database. You'll be sure to have a consistent database, and that's one of the good reasons to use a database. Especially if you have several applications requesting it (or just one application but with a direct mode and a batch mode using different sources).
With MySQL you do not have advanced constraints like you would have in postgreSQL but at least the foreign key constraints are quite advanced.
We'll take an example, a company table with a user table containing people from theses company
CREATE TABLE COMPANY (
company_id INT NOT NULL,
company_name VARCHAR(50),
PRIMARY KEY (company_id)
) ENGINE=INNODB;
CREATE TABLE USER (
user_id INT,
user_name VARCHAR(50),
company_id INT,
INDEX company_id_idx (company_id),
FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;
Let's look at the ON UPDATE clause:
- ON UPDATE RESTRICT : the default : if you try to update a company_id in table COMPANY the engine will reject the operation if one USER at least links on this company.
- ON UPDATE NO ACTION : same as RESTRICT.
- ON UPDATE CASCADE : the best one usually : if you update a company_id in a row of table COMPANY the engine will update it accordingly on all USER rows referencing this COMPANY (but no triggers activated on USER table, warning). The engine will track the changes for you, it's good.
- ON UPDATE SET NULL : if you update a company_id in a row of table COMPANY the engine will set related USERs company_id to NULL (should be available in USER company_id field). I cannot see any interesting thing to do with that on an update, but I may be wrong.
And now on the ON DELETE side:
- ON DELETE RESTRICT : the default : if you try to delete a company_id Id in table COMPANY the engine will reject the operation if one USER at least links on this company, can save your life.
- ON DELETE NO ACTION : same as RESTRICT
- ON DELETE CASCADE : dangerous : if you delete a company row in table COMPANY the engine will delete as well the related USERs. This is dangerous but can be used to make automatic cleanups on secondary tables (so it can be something you want, but quite certainly not for a COMPANY<->USER example)
- ON DELETE SET NULL : * handful* : if you delete a COMPANY row the related USERs will automatically have the relationship to NULL. If Null is your value for users with no company this can be a good behavior, for example maybe you need to keep the users in your application, as authors of some content, but removing the company is not a problem for you.
usually my default is: ON DELETE RESTRICT ON UPDATE CASCADE. with some ON DELETE CASCADE for track tables (logs--not all logs--, things like that) and ON DELETE SET NULL when the master table is a 'simple attribute' for the table containing the foreign key, like a JOB table for the USER table.
Edit
It's been a long time since I wrote that. Now I think I should add one important warning. MySQL has one big documented limitation with cascades. Cascades are not firing triggers. So if you were over confident enough in that engine to use triggers you should avoid cascades constraints.
MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server
Triggers are not activated by foreign key actions.
And I do not think this will get fixed one day. Foreign key constraints are managed by the InnoDb storage and Triggers are managed by the MySQL SQL engine. Both are separated. Innodb is the only storage with constraint management, maybe they'll add triggers directly in the storage engine one day, maybe not.
But I have my own opinion on which element you should choose between the poor trigger implementation and the very useful foreign keys constraints support. And once you'll get used to database consistency you'll love PostgreSQL.
这篇关于外键约束：何时使用ON UPDATE和ON DELETE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
