使用REFERENCES与和不与FOREIGN KEY之间的区别？ [英] Difference between using REFERENCES with and without FOREIGN KEY?

问题描述

基本上我想知道使用REFERENCES与外键之间的区别。

我有这两个例子：

  CREATE TABLE Employee 
（
 id INT，
 name VARCHAR（50），
 birthYear INT，
 boss INT REFERENCES Employees（id），
 worksAt INT NOT NULL参考部门（id）ON DELETE CASCADE，
 PRIMARY KEY（id，worksAt）
）; 
  

示例2：

  CREATE TABLE部门
（
 id INT PRIMARY KEY，
 name VARCHAR（50），
 numberOfEmployees INT，
位置INT NOT NULL，
国家INT NOT NULL，
经理INT，
 FOREIGN KEY（位置，国家）REFERENCES位置（locId，countryId），
 UNIQUE KEY（经理）
）; 
 

我要问的是第二个例子为什么使用FOREIGN KEY关键字，而第一个只是使用REFERENCES。

此外，第一个似乎引用自己（我认为在Employees中的s是一个错误）如果是，为什么使用REFERENCES如果它引用

解决方案

恭喜您！你绊倒了MySQL的一个怪异的奇怪。第一个语法绝对没有。 或支持内联REFERENCES规范（如SQL标准中定义），其中引用被定义为列规范的一部分。 InnoDB仅当指定为单独的FOREIGN KEY规范的一部分时才接受REFERENCES子句。对于其他存储引擎，MySQL服务器解析并忽略外键规范。

create table 文档。

Basically I'd like to know the difference between using REFERENCES with or without a foreign key.

I have these 2 examples:

CREATE TABLE Employee 
(
     id        INT,
     name      VARCHAR(50),
     birthYear INT,
     boss      INT REFERENCES Employees(id),
     worksAt   INT NOT NULL REFERENCES Department(id) ON DELETE CASCADE,
     PRIMARY KEY (id,worksAt)
);

Example 2:

CREATE TABLE Department 
(
      id                INT PRIMARY KEY,
      name              VARCHAR(50),
      numberOfEmployees INT,
      location          INT NOT NULL,
      country           INT NOT NULL,
      manager           INT,
      FOREIGN KEY (location,country) REFERENCES Location(locId,countryId),
      UNIQUE KEY (manager)
);

What I'm asking here is why does the second example use the FOREIGN KEY keyword while the first one merely uses REFERENCES.

Also, the first one seems to reference itself (I think the s in Employees is a mistake) If so, why does it use REFERENCES if it's referencing itself?

解决方案

Congratulations! You've stumbled upon one of the weirder quirks of MySQL. The first syntax does absolutely nothing. It's silently, yes, silently ignored.

Furthermore, InnoDB does not recognize or support "inline REFERENCES specifications" (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications.

It's a little under halfway down the create table documentation.

这篇关于使用REFERENCES与和不与FOREIGN KEY之间的区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！