MySQL InnoDB约束不起作用

本文介绍了MySQL InnoDB约束不起作用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我使用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约束不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

wtf