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

Foreign key not working in MySQL: Why can I INSERT a value that's not in the foreign column?

提问by stalepretzel

I've created a table in MySQL:

我在 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));

This created the table I wanted just fine (although a "DESCRIBE actions;" command showed me that the foreign keys were keys of type MUL, and I'm not sure what this means). However, when I try to enter a Q_id or a U_id that does not exist in the questions or users tables, MySQL still allows these values.

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

What did I do wrong? How can I prevent a table with a foreign key from accepting invalid data?

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

UPDATE 1

更新 1

If I add TYPE=InnoDBto the end, I get an error:

如果我添加TYPE=InnoDB到最后，我会收到一个错误：

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

错误 1005 (HY000): 无法创建表 './quotes/actions.frm' (errno: 150)

Why might that happen?

为什么会发生这种情况？

UPDATE 2

更新 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 不应与 MySQL 一起使用。你有什么建议吗？

回答by Bill Karwin

I would guess that your default storage engine is MyISAM, which ignores foreign key constraints. It silently accepts the declaration of a foreign key, but does not store the constraint or enforce it subsequently.

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

However, it does implicitly create an index on the columns you declared for the foreign key. In MySQL, "KEY" is a synonym for "INDEX". That's what's being shown in the DESCRIBE output: an index, but not a constraint.

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

You are able to insert invalid values to the table right now because there is no constraint. To get a constraint that enforces referential integrity, you must use the InnoDB storage engine:

您现在可以向表中插入无效值，因为没有约束。要获得强制引用完整性的约束，您必须使用 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;

I've always thought it was a big mistake on MySQL's part to silentlyignore foreign key constraint declarations. There's no error or warning that the storage engine doesn't support them.

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

The same is true for CHECK constraints. By the way no storage engine used with MySQL supports CHECK constraints but the SQL parser accepts them with no complaint.

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

---

---

The errno 150 issue occurs when it cannot create the InnoDB table, because it couldn't make sense of the foreign key constraint. You can get some more information with:

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

SHOW ENGINE INNODB STATUS;

Some requirements for InnoDB foreign keys:

InnoDB 外键的一些要求：

• Referenced table must also be InnoDB.
• Referenced table must have an index and a primary key.
• SQL data types of FK column and referenced PK column must be identical. For example, INT does not match BIGINT or INT UNSIGNED.

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

---

---

You can change the storage engine of a table that has data in it:

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

ALTER TABLE actions ENGINE=InnoDB;

This effectively copies the entire MyISAM table to an InnoDB table, then once that succeeds it drops the MyISAM table and renames the new InnoDB table to the name of the former MyISAM table. This is called a "table restructure" and it can be time-consuming, depending on how much data is in the table. A table restructure occurs during ALTER TABLE, even in some cases where it may seem unnecessary.

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

---

---

Re your update 2:

重新更新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 不应与 MySQL 一起使用。你有什么建议吗？

Who told you that? It's absolutely false. InnoDB has better performance than MyISAM(though InnoDB needs more attention to tuning the configuration), InnoDB supports atomic changes, transactions, foreign keys, and InnoDB is much more resistant to corrupting data in a crash.

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

Unless you're running an old, unsupported version of MySQL (5.0 or earlier) you should use InnoDB as your defaultstorage engine choice, and use MyISAM only if you can demonstrate a specific workload that benefits from MyISAM.

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

回答by tttthet

Just to save other's of the hours of headache I've been thru - as giraffa touches upon, ensure @FOREIGN_KEY_CHECKS is set to 1.

只是为了节省其他人的头痛时间 - 正如长颈鹿所说，确保@FOREIGN_KEY_CHECKS 设置为 1。

SELECT @@FOREIGN_KEY_CHECKS

选择@@FOREIGN_KEY_CHECKS

SET FOREIGN_KEY_CHECKS=1

设置 FOREIGN_KEY_CHECKS=1

回答by user203212

I know this thread was opened long time ago, but I am posting this message for future users who will look for the answer. I was having the same problem with foreign key in mysql. The following thing worked for me.

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

Parent table:

父表：

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

Child Table:

子表：

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" did the magic for me.

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

I hope this works for others. Best of luck.

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

回答by user203212

the problem is most likely that questions.p_id and users.p_id are not defined as INT NOT NULL. for foreign keys to work, the definition of the columns on both side of the foreign key must match exactly, with the exception of auto_increment and default.

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

回答by stalepretzel

I found the following article. I don't have time to test it out, currently, but it may be helpful:

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

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

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

The author,Edwin Dando, says:

作者埃德温·丹多 (Edwin Dando) 说：

both tables must be INNODB. The foreign key field must have an index on it. The foeign key field and the field being referenced must be of the same type (I only use integer) and, after hours of pain, they must be UNSIGNED.

两个表都必须是 INNODB。外键字段上必须有索引。外国键字段和被引用的字段必须是相同类型的（我只使用整数），经过几个小时的痛苦之后，它们必须是未签名的。

回答by SlothOfDoom

This answer would have saved me a lot of time if I'd seen it first. Try the following three steps, which I've ordered by frequency of newbie mistakes:

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

(1) Change the table to be InnodDB by appending "ENGINE=InnoDB" to your "CREATE TABLE" statements.

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

Other engines, which may be the default, do not support foreign key constraints, but neither do they throw an error or warning telling you they're not supported.

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

(2) Make sure foreign key constraints are in fact being checked by executing "SET foreign_key_checks = 'ON'"

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

(3) Append "ON UPDATE CASCADE" to your foreign key declaration.

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

Note: Make sure that cascading is the behavior you want. There are other options...

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

回答by Issa

Well, my guess is somehow the "Skip creation of FORIEN KEYS" option is checked, it can happen in the "options" section of the "Forward Engineering" process.

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

回答by chaos

As noted, your table have to be InnoDB for FK constraints to be enforced.

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

I've only run into the 'Can't create table' in the case where I'm trying to create a foreign key constraint where my local column is a different type from the foreign column.

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

回答by bluegrass

I think some of the folks having this problem might be starting out with some of the sample databases provided on the ORACLE website for MYSQL (e.g. sakila DB). Don't forget to "turn the foreign key constraints back on" at the end of your script (e.g. at the beginning of sakila DB script they are turned OFF)

我认为一些有这个问题的人可能是从 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';

create your tables here

在这里创建您的表格

then don't forget this:

然后不要忘记这个：

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

回答by user9349193413

For those who still have problems with mysql ignoring foreign keys constraints and for those who the answers above or in any other related question didn't solve teir puzzle, here is what I found to be the issue.

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

If you declare your foreign keys as such

如果你这样声明你的外键

id  INTEGER UNSIGNED    REFERENCES    A_Table(id)

Then the foreign key seems to be ignored, to enforce the constraint without (apparently) having to use any of the SET commands, use the following declaration.

然后外键似乎被忽略了，为了在不（显然）必须使用任何 SET 命令的情况下强制执行约束，请使用以下声明。

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

This way solved the problem for me. Not sure why, as many say the first declaration is only a shorthand to the second variant.

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