外键在 MySQL 中不起作用:为什么我可以插入一个不在外列中的值? [英] Foreign key not working in MySQL: Why can I INSERT a value that's not in the foreign column?

问题描述

我在 MySQL 中创建了一个表:

I've created a table in 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 仍然允许这些值.

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.

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

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

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

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

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

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

为什么会这样?

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

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?

推荐答案

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

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.

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

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.

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

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:

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

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

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

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.

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

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:

SHOW ENGINE INNODB STATUS;

对 InnoDB 外键的一些要求:

Some requirements for InnoDB foreign keys:

• 引用的表也必须是 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;

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

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.

关于你的更新 2:

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

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 的性能优于 MyISAM(尽管 InnoDB 需要更多关注 调整配置)，InnoDB 支持原子更改、事务、外键，并且 InnoDB 在崩溃时更能抵抗损坏数据.

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.

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

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

这篇关于外键在 MySQL 中不起作用:为什么我可以插入一个不在外列中的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！