MySQL的外键'on delete restrict'子句的确切含义 [英] Exact Meaning of MySQL's Foreign Key 'on delete restrict' Clause

问题描述

我有两个 MySQL 表:collections 和 privacy_level.
我用 外键 关系来定义它们:

I have two MySQL tables: collections and privacy_level.
I define them with a foreign key relationship as such:

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;  

我的问题是关于 ON DELETE RESTRICT 子句，我无法从在线手册或谷歌搜索中得到答案.

My question is about the ON DELETE RESTRICT clause and I couldn't derive the answer from the online manual or a google search.

这是否意味着我可以从不从 privacy_level 中删除一行?
或者，这是否意味着我不能从 privacy_level 中删除一行 如果从 collections.privacy 中删除一行是否具有与 privacy_level.level 中的值相同的值?

Does this mean that I can never delete a row from privacy_level?
Or, does it mean that I can't delete a row from privacy_level if a row from collections.privacy has a value that is the same as a value in privacy_level.level?

也就是说，如果 privacy_level 有 level = 2，name = 'top secret' 但集合中没有条目.Privacy 有 privacy = 2，我可以删除 level = 2, name = 'top secret' 条目吗?还是在列范围内禁止?

That is, if privacy_level has 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?

感谢您的任何见解.

推荐答案

ON DELETE RESTRICT 表示你不能删除给定的父行 如果存在引用该父行值的子行.如果父行没有引用子行，则可以删除该父行.

ON DELETE RESTRICT means you can't delete a given parent row if a child row exists that 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 RESTRICT is pretty much superfluous syntax, because this is the default behavior for a foreign key anyway.

这篇关于MySQL的外键'on delete restrict'子句的确切含义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！