SHOW ENGINE INNODB STATUS的以下结果显示了令人讨厌的ADD FOREIGN KEY查询，但我不知道为什么它会失败。引用都可以，mysql检查也不给出任何错误。
在底部，我还为两个表添加了SHOW CREATE TABLE的结果。
此错误的原因是什么？
LATEST FOREIGN KEY ERROR
------------------------
130628 18:28:22 Transaction:
TRANSACTION 21F28, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
18 lock struct(s), heap size 3112, 1304 row lock(s), undo log entries 1174
MySQL thread id 12864, OS thread handle 0x7f8171f8d700, query id 107567 localhost root copy to tmp table
ALTER TABLE `entry` ADD FOREIGN KEY (`journal`) REFERENCES `aqqounts`.`journal`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
Foreign key constraint fails for table `aqqounts`.`#sql-389_3240`:
,
CONSTRAINT `?sql?389_3240_ibfk_1` FOREIGN KEY (`journal`) REFERENCES `journal` (`id`)
Trying to add in child table, in index `journal` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 00000199; asc ;;
1: len 2; hex 0cfe; asc ;;
But in parent table `aqqounts`.`journal`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 0000019a; asc ;;
1: len 6; hex 000000021ec9; asc ;;
2: len 7; hex 6500000e3b2f19; asc e ;/ ;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 51cda573; asc Q s;;
5: len 4; hex 51cdac00; asc Q ;;
6: len 3; hex 8fb39e; asc ;;
7: len 4; hex 00000000; asc ;;
| entry | CREATE TABLE `entry` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`journal` int(11) unsigned NOT NULL,
`date` date DEFAULT NULL,
`ledger` int(4) NOT NULL DEFAULT '2900',
`amount` decimal(12,2) NOT NULL,
`vat` int(2) NOT NULL DEFAULT '0',
`relation` varchar(30) NOT NULL,
`description` varchar(250) NOT NULL,
PRIMARY KEY (`id`),
KEY `journal` (`journal`)
) ENGINE=InnoDB AUTO_INCREMENT=5301 DEFAULT CHARSET=utf8 |
| journal | CREATE TABLE `journal` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user` int(11) unsigned DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'trigger is needed',
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`date` date DEFAULT NULL,
`account` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1042 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
最佳答案
可能是因为您已经在entry列下的表journal中存储了无效数据，例如如果您在entry中有一个记录，其中journal = 1，但是在journal中没有该记录。
另外，您应该尝试使用相同的名称来命名外键，例如journal。journalId和entry。journalId
关于mysql - mysql外键错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17370542/