MySQL 外键“删除限制”条款的确切含义
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8072876/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exact Meaning of MySQL's Foreign Key 'on delete restrict' Clause
提问by Donkey Trouble
I have two MySQL tables: collectionsand privacy_level.
I define them with a foreign keyrelationship as such:
我有两个 MySQL 表：collections和privacy_level.
我用这样的外键关系定义它们：
CREATE TABLE collections (
coll_id smallint NOT NULL AUTO_INCREMENT UNSIGNED,
name varchar(30) NOT NULL,
privacy tinyint NOT NULL UNSIGNED DEFAULT '0',
PRIMARY KEY(coll_id),
INDEX(privacy),
FOREIGN KEY fk_priv (privacy) REFERENCES privacy_level (level) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE privacy_level (
level tinyint NOT NULL UNSIGNED,
name varchar(20) NOT NULL,
PRIMARY KEY (level)
) ENGINE InnoDB;
My question is about the ON DELETE RESTRICTclause and I couldn't derive the answer from the online manual or a google search.
我的问题是关于ON DELETE RESTRICT条款，我无法从在线手册或谷歌搜索中得到答案。
Does this mean that I can neverdelete a row from privacy_level?
Or, does it mean that I can'tdelete a row from privacy_levelifa row from collections.privacyhas a value that is the same as a value in privacy_level.level?
这是否意味着我永远不能从中删除一行privacy_level？
或者，这是否意味着如果from 中的行的值与 中的值相同，我无法删除该行？privacy_levelcollections.privacyprivacy_level.level
That is, if privacy_levelhas level = 2, name = 'top secret'but no entry in collections.Privacy has privacy = 2, can I delete the level = 2, name = 'top secret'entry? Or is it forbidden on a column wide basis?
也就是说，如果privacy_level有level = 2，name = 'top secret'但 collections.Privacy 中没有条目privacy = 2，我可以删除level = 2,name = 'top secret'条目吗？还是在列范围内禁止？
Thanks for any insight.
感谢您的任何见解。
回答by Bill Karwin
ON DELETE RESTRICTmeans you can'tdelete a given parent rowif a child row existsthat references the value for that parent row. If the parent row has no referencing child rows, then you can delete that parent row.
ON DELETE RESTRICT意味着你不能删除一个给定的父行，如果一个孩子行存在引用，该父行的值。如果父行没有引用子行，则可以删除该父行。
ON DELETE RESTRICTis pretty much superfluoussyntax, because this is the default behavior for a foreign key anyway.
ON DELETE RESTRICT几乎是多余的语法，因为无论如何这是外键的默认行为。
回答by jose miguel rivera rodríguez
Also you can use ON DELETE CASCADE, that means when you delete the parent all the children will be removed automatically, this is useful when you have a table associated with another that contains some parameters or settings.
您也可以使用ON DELETE CASCADE，这意味着当您删除父项时，所有子项都将被自动删除，当您有一个与另一个包含某些参数或设置的表关联时，这很有用。
