外键在 MySQL 中不起作用：为什么我可以插入不在外键列中的值？

Question

我已经在 MySQL 中创建了一个表：

CREATE TABLE actions ( A_id int NOT NULL AUTO_INCREMENT,
type ENUM('rate','report','submit','edit','delete') NOT NULL,
Q_id int NOT NULL,
U_id int NOT NULL,
date DATE NOT NULL,
time TIME NOT NULL,
rate tinyint(1),
PRIMARY KEY (A_id),
CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id),
CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id));

这很好地创建了我想要的表（尽管“DESCRIBE actions;”命令显示外键是 MUL 类型的键，我不确定这意味着什么）。 但是，当我尝试输入问题或用户表中不存在的 Q_id 或 U_id 时，MySQL 仍然允许这些值。

我做错了什么？ 如何防止具有外键的表接受无效数据？

更新 1

如果我将 TYPE=InnoDB 添加到末尾，则会收到错误：

错误 1005 (HY000)：无法创建表“./quotes/actions.frm”（错误号：150）

为什么会发生这种情况？

UPDATE 2

我被告知使用功能外键强制数据完整性很重要，而且 InnoDB 不应该与 MySQL 一起使用。 你有什么建议吗？

Answer 1

我猜你的默认存储引擎是MyISAM，它忽略外键约束。 它默默地接受外键的声明，但不存储约束或随后强制执行它。

但是，它确实会在您为外键声明的列上隐式创建索引。 在 MySQL 中，“KEY”是“INDEX”的同义词。 这就是 DESCRIBE 输出中显示的内容：索引，但不是约束。

您现在可以向表中插入无效值，因为没有约束。 要获得强制引用完整性的约束，必须使用 InnoDB 存储引擎：

CREATE TABLE actions (
  A_id int NOT NULL AUTO_INCREMENT,
  ...
  CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id),
  CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id)
) ENGINE=InnoDB;

我一直认为 MySQL 默默地忽略外键约束声明是一个很大的错误。 没有错误或警告表明存储引擎不支持它们。

MySQL 8.0.16之前的CHECK约束也是如此。 MySQL 使用的存储引擎均不支持 CHECK 约束，但 SQL 解析器毫无怨言地接受它们。 在8.0.16及更高版本中，InnoDB表确实支持CHECK约束，但其他存储引擎仍然忽略它们。

---

当无法创建 InnoDB 表时，会出现 errno 150 问题，因为它无法理解外键约束。 您可以通过以下方式获得更多信息：

SHOW ENGINE INNODB STATUS;

InnoDB 外键的一些要求：

• 引用的表也必须是 InnoDB。
• 引用的表必须有索引和主键。
• FK 列和引用的 PK 列的 SQL 数据类型必须相同。 例如，INT 与 BIGINT 或 INT UNSIGNED 不匹配。

---

您可以更改其中包含数据的表的存储引擎：

ALTER TABLE actions ENGINE=InnoDB;

这会有效地将整个 MyISAM 表复制到 InnoDB 表，然后一旦成功，它就会删除 MyISAM 表并将新的 InnoDB 表重命名为以前的 MyISAM 表的名称。 这称为“表重组”，它可能非常耗时，具体取决于表中的数据量。 表重组发生在 ALTER TABLE 期间，即使在某些情况下似乎没有必要。

---

回复您的更新2：

有人告诉我，使用功能外键强制数据完整性很重要，但 InnoDB 不应该与 MySQL 一起使用。 你有什么推荐？

谁告诉你的？ 这绝对是假的。 InnoDB 的性能优于MyISAM（尽管InnoDB需要更多关注调整配置），InnoDB 支持原子更改、事务、外键，并且 InnoDB 在崩溃时更能抵抗数据损坏。

除非您运行的是旧的、不受支持的 MySQL 版本（5.0 或更早版本），否则您应该使用 InnoDB 作为您的默认存储引擎选择，并且仅当您可以演示受益于 MyISAM 的特定工作负载时才使用 MyISAM 。

Answer 2

只是为了避免我经历过的头痛 - 正如 giraffa 所提到的，确保 @FOREIGN_KEY_CHECKS 设置为 1。

SELECT @@FOREIGN_KEY_CHECKS

SET FOREIGN_KEY_CHECKS=1

Answer 3

我知道这个线程很久以前就打开了，但我发布此消息是为了将来寻找答案的用户。
我在 mysql 中的外键上遇到了同样的问题。
以下事情对我有用。

父表：

CREATE TABLE NameSubject (
  Autonumber INT NOT NULL AUTO_INCREMENT,
  NameorSubject nvarchar(255),
  PRIMARY KEY (Autonumber)
 ) ENGINE=InnoDB;

子表：

CREATE TABLE Volumes (
  Autonumber INT NOT NULL,
  Volume INT,
  Pages nvarchar(50),
  Reel int,
  Illustrations bit,
  SSMA_TimeStamp timestamp,
  Foreign KEY (Autonumber) references NameSubject(Autonumber)
  ON  update cascade 
)engine=innodb;

“更新级联”对我来说很神奇。

我希望这对其他人有用。 祝你好运。

Answer 4

对于那些仍然遇到 mysql 忽略外键约束问题的人，以及那些上面的答案或任何其他相关问题没有解决他们的难题的人，这就是我发现的问题。

如果您这样声明外键，

id  INTEGER UNSIGNED    REFERENCES    A_Table(id)

那么外键似乎会被忽略，要强制执行约束而无需（显然）使用任何 SET 命令，请使用以下声明。

id  INTEGER UNSIGNED,
CONSTRAINT fk_id  FOREIGN KEY (id)  REFERENCES A_Table(id)

这种方式为我解决了问题。 不知道为什么，正如许多人所说，第一个声明只是第二个变体的简写。

Answer 5

有同样的问题。 我的案例的解决方案是将存储引擎从 MyISAM 更改为 InnoDB，之后它就完美运行了。 对于我作为一个非 mysql 专业人士来说，这似乎很愚蠢，但也许更高级的程序员知道这种行为的原因。

Answer 6

我找到了下面的文章。 目前我没有时间测试它，但它可能会有所帮助：

http://forums.mysql.com/read.php?22,19755,43805

作者 Edwin Dando 说：

两个表都必须是 INNODB。 外键字段必须有索引。 外部键字段和被引用的字段必须具有相同的类型（我只使用整数），并且经过几个小时的痛苦，它们必须是无符号的。

Answer 7

问题很可能是 questions.p_id 和 users.p_id 没有定义为 INT NOT NULL。 为了使外键发挥作用，外键两侧的列定义必须完全匹配，但 auto_increment 和 default 除外。

Answer 8

如果我先看到这个答案，我会节省很多时间。 尝试以下三个步骤，我按照新手错误的频率排序：

(1) 通过将“ENGINE=InnoDB”附加到“CREATE TABLE”语句中，将表更改为 InnoDB。

其他引擎（可能是默认引擎）不支持外键约束，但它们也不会抛出错误或警告告诉您它们不受支持。

(2) 通过执行“SETforeign_key_checks = 'ON'”确保实际上正在检查外键约束。

(3) 将“ON UPDATE CASCADE”附加到外键声明中。

注意：确保级联是您想要的行为。 还有其他选择...

Answer 9

如前所述，您的表必须是 InnoDB 才能强制执行 FK 约束。

我仅在尝试创建外键约束（其中本地列与外部列的类型不同）的情况下遇到“无法创建表”。

Answer 10

我认为一些遇到此问题的人可能会从 ORACLE 网站上提供的 MYSQL 示例数据库（例如 sakila DB）开始。 不要忘记在脚本末尾“重新打开外键约束”（例如，在 sakila DB 脚本的开头，它们被关闭）

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

在此处创建表，

然后不要忘记这一点：

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 11

好吧，我的猜测是“跳过创建 FORIEN KEYS”选项被选中，它可能发生在“正向工程”过程的“选项”部分。