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)

我们得到了空集，这没关系，因为 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 的结果.哇！

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.

仅仅发布一个架构并不能说明您受到保护.

Simply publishing a schema does little to say you are safeguarded.

意思是，我可以关闭我的约束，使用系统，删除一些数据，执行 LOAD DATA INFILE(简而言之，把我的数据弄乱)，然后带着一个模式跑到 Stackoverflow 并说天哪，这是怎么发生的".

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