MySQL错误: 1005无法创建表'myTable‘(错误号: 150)
MySQL错误: 1005无法创建表'myTable‘(错误号: 150)
提问于 2012-03-01 00:59:11
我已经阅读了很多关于这个错误的帖子，但没有一个解决方案能够解决这个问题(假设我已经正确地尝试过了)。
以下是导致错误的代码：
CREATE TABLE season
(
id smallint unsigned NOT NULL auto_increment,
title varchar(25) NOT NULL,
PRIMARY KEY (id)
);
CREATE INDEX seasonId ON season(id);
DROP TABLE IF EXISTS event;
CREATE TABLE event
(
id smallint unsigned NOT NULL auto_increment,
title varchar(255) NOT NULL,
season_id smallint NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (season_id) REFERENCES season(id)
ON UPDATE RESTRICT ON DELETE RESTRICT
);所以根据错误，我的外键声明有问题。然而，我已经在机器上运行了这段代码，没有任何问题，它在我的Linux机器上也运行得很好(我目前在Windows7下工作)。
下面是SHOW ENGINE INNODB STATUS的输出
------------------------
LATEST FOREIGN KEY ERROR
------------------------
120229 17:43:28 Error in foreign key constraint of table fcrcontent/event:
FOREIGN KEY (season_id) REFERENCES season(id)
ON UPDATE RESTRICT ON DELETE RESTRICT
):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.我还尝试在一个新的数据库上运行我的脚本，但没有成功。
以下是show create table season的输出
| season | CREATE TABLE `season` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(25) NOT NULL,
PRIMARY KEY (`id`),
KEY `seasonId` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |Stack Overflow用户
回答已采纳
发布于 2012-03-01 01:43:08
由于season.id未签名，因此event.season_id也需要未签名：
CREATE TABLE event
(
id smallint unsigned NOT NULL auto_increment,
title varchar(255) NOT NULL,
season_id smallint unsigned NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (season_id) REFERENCES season(id)
ON UPDATE RESTRICT ON DELETE RESTRICT
);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9503334
复制相关文章
相似问题
腾讯云开发者
