mysql - 外键在 MySQL : Why can I INSERT a value that's not in the foreign column? 中不起作用

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 类型的键，我不确定这意味着什么)。但是，当我尝试输入 questions 或 users 表中不存在的 Q_id 或 U_id 时，MySQL 仍然允许这些值。

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

更新 1

如果我将 TYPE=InnoDB 添加到末尾，则会出现错误:

ERROR 1005 (HY000): Can't create table './quotes/actions.frm' (errno: 150)

为什么会这样？

更新 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 的一个大错误。没有错误或警告存储引擎不支持它们。

对于 CHECK 约束也是如此。顺便说一句，没有与 MySQL 一起使用的存储引擎支持 CHECK 约束，但 SQL 解析器毫无怨言地接受它们。

---

当它无法创建 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:

I'm told that it's important to enforce data integrity with functional foreign keys, but also that InnoDB should not be used with MySQL. What do you recommend?

谁告诉你的？这是绝对错误的。 InnoDB has better performance than MyISAM (尽管 InnoDB 需要更多关注 tuning the configuration )，InnoDB 支持原子更改、事务、外键，并且 InnoDB 更能抵抗崩溃中的损坏数据。

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