MySQL错误：1005不能创建表'myTable'（错误：150）

问题描述

我已经阅读了大量关于这个错误的文章，但是没有一个解决方案能够解决这个问题（假设我已经正确地尝试了这些）。

这是导致错误的代码：

  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事件
（
 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机器上运行得非常好（我目前在Windows 7下工作）。

以下是显示引擎INNODB状态的输出：

  --- --------------------- 
最新的外键错误
 ----------------- ------- 
 120229 17:43:28表fcrcontent / event的外键约束错误：
 FOREIGN KEY（season_id）REFERENCES season（id）
 ON UPDATE RESTRICT ON DELETE RESTRICT 
）：
在引用的表中找不到索引，其中引用
的列显示为第一列，或者表中的列类型为
，引用的表不引用匹配约束。 
请注意，在> = InnoDB-4.1.12创建的
表中，ENUM和SET的内部存储类型发生了变化，旧表
中的这样的列不能被新的表。 
正确的外键定义见http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html 
。 
  

我也尝试在新的数据库上运行我的脚本，但是没有去。

以下是从 show create table 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 | 
  

解决方案

由于season.id是无符号的，event.season_id需要进行无符号的处理：

$ pre $ code $ CREATE TABLE
$ smallint unsigned NOT NULL auto_increment
title varchar（255）NOT NULL，
season_id smallint unsigned NOT NULL，
$ b PRIMARY KEY（id），
FOREIGN KEY（season_id）REFERENCES season（id）
在UPDATE RESTRICT上删除限制
）;

I've read a number of posts about this error, but none of the solutions have managed to solve the problem (assuming I've tried them correctly).

This is the code that causes the error:

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
);

So according to the error there is a problem with my foreign key declaration. However I had already run this code on the machine with no problems, and it ran perfectly on my Linux Machine as well (I'm currently working under Windows 7).

Here is the output of 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.

I also tried running my script on a fresh database, but no go.

Here is the output from 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 |

解决方案

Since season.id is unsigned, event.season_id also needs to be unsigned:

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
);

这篇关于MySQL错误：1005不能创建表'myTable'（错误：150）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！