mysql innodb不生效,MySQL 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)

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)

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

How to explain this situation?

Why constraint does not work?

Any idea, thanks.

解决方案

If a call was made to

SET FOREIGN_KEY_CHECKS=0;

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;

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.

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.