MySQL外键“关于删除限制"子句的确切含义 [英] 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子句的，我无法从在线手册或Google搜索中得出答案.

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 = 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外键“关于删除限制"子句的确切含义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！