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)
我们得到了空集,这没关系,因为 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.
相关文章