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

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

更新1

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

ERROR 1005 (HY000): 无法创建表 './quotes/actions.frm'（errno: 150）

为什么会发生这种情况？

更新2

据我所知，使用功能性外键强制执行数据完整性很重要，但也有人说InnoDB不应与MySQL一起使用。你有什么建议？

- stalepretzel

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约束，但其他存储引擎仍将其忽略。

---

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

SHOW ENGINE INNODB STATUS;

InnoDB 外键的一些要求：

• 被引用的表也必须是 InnoDB。
• 被引用的表必须有一个索引和一个主键。
• 外键列和被引用主键列的 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。

- Bill Karwin

---

感谢您的回答 - 省了我很多时间。顺便说一下，要将默认引擎从MyISAM更改为InnoDB，请在[mysqld]部分的my.cnf文件中添加"default-storage-engine = innodb"。 - Dave

@HDave：很高兴能帮忙！是的，在最近的MySQL版本中，没有理由不将innodb设置为默认值，在MySQL 5.5中也是如此。 - Bill Karwin

很好的解释。 - Shalika

哇，讲解得非常好。谢谢伙计。 - Aleem

Answer 2

为了避免其他人像我一样浪费时间头疼 - 如giraffa所提到的，确保@FOREIGN_KEY_CHECKS设置为1。

SELECT @@FOREIGN_KEY_CHECKS

SET FOREIGN_KEY_CHECKS=1

- tttthet

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;

"

ON update cascade

" 对我很有帮助。希望对其他人也有用。祝好运。

- user203212

Answer 4

我曾经遇到过同样的问题。在我的情况下，解决方案是将存储引擎从MyISAM更改为InnoDB，然后它就完美地工作了。对于像我这样的非MySQL专业人员来说，这似乎很愚蠢，但也许更高级的程序员知道这种行为的原因。

- paulalexandru

Answer 5

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

如果你将外键声明为如下形式：

id  INTEGER UNSIGNED    REFERENCES    A_Table(id)

如果外键被忽略了，想要强制执行约束而不必使用任何SET命令，可以使用以下声明。

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

这种方法对我解决了问题。不确定为什么，因为许多人说第一个声明只是第二种变体的简写。

- user9349193413

Answer 6

如果我先看到这篇回答，就可以省去很多时间。尝试以下按新手错误频率排序的三个步骤： (1)在"CREATE TABLE"语句中附加"ENGINE=InnoDB"来将表更改为InnodDB。 其他引擎可能是默认值，不支持外键约束，但它们也不会抛出告诉您它们不支持的错误或警告。 (2)通过执行"SET foreign_key_checks ='ON'"确保实际上正在检查外键约束。 (3)在外键声明中附加"ON UPDATE CASCADE"。 注意:确保级联是您想要的行为。还有其他选项...

- SlothOfDoom

Answer 7

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

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

作者 Edwin Dando 表示：

两个表都必须是 INNODB 引擎。外键字段必须有索引。外键字段和被引用的字段必须是相同类型（我只使用整数），并且经过长时间的痛苦后，它们必须是 UNSIGNED。

- stalepretzel

---

在早期版本的MySQL中，在声明外键约束之前，必须手动在列上创建索引。在更近期的版本中，如果需要，在声明FK时索引会隐式地为您创建。 - Bill Karwin

Answer 8

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

- longneck

Answer 9

我认为一些遇到这个问题的人可能是通过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;

- bluegrass

Answer 10

嗯，我猜测可能是“跳过创建外键”选项被勾选了，在“正向工程”过程的“选项”部分中可能会发生这种情况。

- Issa