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

问题描述

  CREATE TABLE操作（A_id int NOT NULL AUTO_INCREMENT，
类型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 到最后，我得到一个错误：
$ b

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

为什么会这样？

更新2

我被告知使用功能性外键强制执行数据完整性非常重要， InnoDB不应该与MySQL一起使用。你推荐什么？

解决方案

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

然而，它隐式地创建索引在你声明的列外键。在MySQL中， KEY 是 INDEX 的同义词。这就是在DESCRIBE输出中显示的内容：一个索引，而不是一个约束。

现在可以向表中插入无效值，因为没有约束。为了得到一个强制执行参照完整性的约束，你必须使用InnoDB存储引擎：
$ b $ pre $ CREATE TABLE操作
A_id诠释NOT NULL AUTO_INCREMENT，
...
约束fk_Question外键（Q_id）参考文献的问题（P_ID），
约束fk_User外键（U_ID）参考文献用户（P_ID）
） ENGINE = InnoDB的;

我一直认为这对于MySQL来说是一个很大的错误 忽略外键约束声明。没有任何错误或警告，说明存储引擎不支持它们。顺便说一句，对于CHECK约束，也是如此。

没有与MySQL一起使用的存储引擎支持CHECK约束，但是SQL解析器在没有任何投诉的情况下接受它们。

---

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

  SHOW ENGINE INNODB STATUS; 
  

InnoDB外键的一些要求：

• 引用表必须也是InnoDB。


• 引用表必须有一个索引和一个主键


• SQL数据FK列和参考PK列的类型必须相同。例如，INT不匹配BIGINT或INT UNSIGNED。
  

---

您可以更改存储引擎中有数据的表：
$ b $ pre $ ALTER TABLE操作ENGINE = InnoDB;


这有效地将整个MyISAM表复制到一个InnoDB表中，一旦成功，它就会丢弃MyISAM表并将新的InnoDB表重命名为以前的MyISAM表的名称。这称为表重组，根据表中有多少数据，这可能是耗时的。在ALTER TABLE期间发生表重组，即使在某些情况下，它可能看起来没有必要。

---

重新更新2： / b>

我被告知使用功能性外键强制执行数据完整性非常重要，而且InnoDB不应该与MySQL一起使用。你推荐什么？

你告诉过你了吗？这绝对是错误的。 InnoDB比MyISAM有更好的性能（尽管InnoDB需要更多的关注 调整配置），InnoDB支持原子更改，事务，外键，而InnoDB更能抵御破坏数据。

<除非你正在运行一个不受支持的老版本的MySQL（5.0或者更早的版本），否则你应该使用InnoDB作为默认的存储引擎选择，并且只有在你能够演示特定的工作负载时才能使用MyISAM来自MyISAM。

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

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?

UPDATE 1

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

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

Why might that happen?

UPDATE 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?

解决方案

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.

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.

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;

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.

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.

---

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;

Some requirements for InnoDB foreign keys:

• 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.

---

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.

---

Re your update 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?

You 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.

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屋！