mysql 外键 失败_存在外键时，MySQL 5.5外键约束失败

刚在Mac OS X 10.6上安装了MySQL

5.5，并且在许多表上都有一个奇怪的问题。下面是一个例子。插入行失败，但不应插入外键约束。它引用的外键确实存在。有任何想法吗？

mysql> show create table Language;

+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Language | CREATE TABLE `Language` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Code` varchar(2) NOT NULL,

`Name` varchar(63) CHARACTER SET utf8 DEFAULT NULL,

`Variant` varchar(63) CHARACTER SET utf8 DEFAULT NULL,

`Country_Id` int(11) DEFAULT NULL,

PRIMARY KEY (`Id`),

UNIQUE KEY `Code` (`Code`,`Country_Id`,`Variant`),

KEY `FKA3ACF7789C1796EB` (`Country_Id`),

CONSTRAINT `FKA3ACF7789C1796EB` FOREIGN KEY (`Country_Id`) REFERENCES `Country` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |

+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show create table Language_Phrases;

+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Language_Phrases | CREATE TABLE `Language_Phrases` (

`Language_Id` int(11) NOT NULL,

`Phrase` varchar(255) DEFAULT NULL,

`Label` varchar(255) NOT NULL,

PRIMARY KEY (`Language_Id`,`Label`),

KEY `FK8B4876F3AEC1DBE9` (`Language_Id`),

CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES `Language` (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from Language;

+----+------+----------+---------+------------+

| Id | Code | Name | Variant | Country_Id |

+----+------+----------+---------+------------+

| 1 | en | English | | 235 |

| 2 | ro | Romanian | | 181 |

+----+------+----------+---------+------------+

2 rows in set (0.00 sec)

mysql> select * from Language_Phrases;

Empty set (0.00 sec)

mysql> INSERT INTO Language_Phrases (Language_Id, Label, Phrase) VALUES (1, 'exampleLabel', 'Some phrase');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dev`.`language_phrases`, CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES `Language` (`Id`))

mysql>

更新 ：多次删除并重新创建数据库show engine innodb

status后，我在上述插入失败后进行了操作，并得到了令人惊讶的结果。找不到父语言表！这似乎很奇怪…有什么想法吗？

------------------------

LATEST FOREIGN KEY ERROR

------------------------

110406 9:55:49 Transaction:

TRANSACTION CA3B, ACTIVE 0 sec, OS thread id 4494462976 inserting

mysql tables in use 1, locked 1

1 lock struct(s), heap size 376, 0 row lock(s)

MySQL thread id 25, query id 50720 localhost root update

INSERT INTO Language_Phrases (Language_Id, Label, Phrase) VALUES (1, 'exampleLabel', 'Some phrase')

Foreign key constraint fails for table `dev`.`language_phrases`:

,

CONSTRAINT `FK8B4876F3AEC1DBE9` FOREIGN KEY (`Language_Id`) REFERENCES `Language` (`Id`)

Trying to add to index `PRIMARY` tuple:

DATA TUPLE: 5 fields;

0: len 4; hex 80000001; asc ;;

1: len 17; hex 747970654d69736d617463682e79656172; asc exampleLabel;;

2: len 6; hex 00000000ca3b; asc ;;;

3: len 7; hex 00000000000000; asc ;;

4: len 21; hex 59656172206d7573742062652061206e756d626572; asc Some phrase;;

But the parent table `dev`.`Language`

or its .ibd file does not currently exist!

更新2 ：事实证明，这只是MySQL中的一个 巨大 错误。显然，最新版本的MySQL无法在mac os X

10.6下完全运行(也许也可以是早期版本？)。降级至5.5.8似乎可行。非常令人惊讶。