MySQL InnoDB约束不起作用 [英] MySQL InnoDB constraint does not work

问题描述

我使用innoDB约束绊倒了奇怪的行为，并且找不到原因。
我有带数据的表。

下面列出了它们的结构：

I stumble upon strange behavior with innoDB constraint, and cannot find cause of it.
I have tables with data.
Below listed their structures:

CREATE TABLE `contents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `type` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nameUNIQUE` (`name`),
  KEY `name` (`name`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dataTable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `value_UNIQUE` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `content_data` (
  `content_id` int(10) unsigned NOT NULL,
  `field_id` int(10) unsigned NOT NULL,
  `data_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`content_id`,`field_id`,`data_id`),
  KEY `fk_content_data_2_idx` (`field_id`),
  KEY `fk_content_data_3_idx` (`data_id`),
  CONSTRAINT `fk_content_data_1` FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_content_data_2` FOREIGN KEY (`field_id`) REFERENCES `fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_content_data_3` FOREIGN KEY (`data_id`) REFERENCES `dataTable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在，我们执行两个查询：

第一个查询：

Now, let's execute two queries:
First query:

mysql> SELECT * FROM `dataTable` where id=21318;
Empty set (0.00 sec)

我们有空集， c $ c> dataTable 实际上没有任何id = 21318的行

第二个查询：

We got empty set and that is ok since dataTable in fact has no any row with id=21318
Second query:

mysql> SELECT * FROM `content_data` where data_id=21318;
+------------+----------+---------+
| content_id | field_id | data_id |
+------------+----------+---------+
|        552 |       35 |   21318 |
+------------+----------+---------+
1 row in set (0.00 sec)

这里，最后一个查询给出了data_id = 21318的结果。 WTF！

Here, last query give us result with data_id=21318. WTF!

如何解释这种情况？

为什么约束不起作用？

任何想法，谢谢。

How to explain this situation?
Why constraint does not work?
Any idea, thanks.

推荐答案

如果致电

SET FOREIGN_KEY_CHECKS=0;

，则FK检查关闭。谁知道，这可能是你的系统的状态现在。让他们开启

then FK checks are turned off. Who knows, that may be the state of your system right now. Have them turned on with

SET FOREIGN_KEY_CHECKS=1;

请注意以下事项。只是重新打开检查不会重新验证参照完整性。需要 ALTER TABLE 。

Note the following. Just turning checks back on does not re-validate the referential integrity. One needs ALTER TABLE for that.

只需发布一个模式，

意思是，我可以关闭我的约束，使用系统，删除一些数据，做LOAD DATA INFILE（简单的搞乱我的数据），然后运行到Stackoverflow并且说gosh是怎么发生的。

Meaning, I could turn off my constraints, use the system, delete some data, do LOAD DATA INFILE (in short mess up my data), then run off to Stackoverflow with a schema and say "gosh how did this happen".

现在你的系统状态并不重要。这很重要，当时是什么。

And it doesn't matter what state your system is in now. It matters what it was in back then.

这篇关于MySQL InnoDB约束不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！