MySql error 1005

今天用到mysql做数据库服务器，建了几张表，在用到外键的时候数据库总是报出MySQL ERROR 1005: Can't create table (errno: 150)错误，不能建立数据库约束条件。

下面是建立表的脚本：

DROP TABLE IF EXISTS `summertestdb`.`moudle`;
CREATE TABLE  `summertestdb`.`moudle` (
  `moudleID` varchar(50) NOT NULL,
  `moudlename` varchar(50) NOT NULL,
  `moudleinfo` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`moudleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

DROP TABLE IF EXISTS `summertestdb`.`role`;
CREATE TABLE  `summertestdb`.`role` (
  `roleID` varchar(50) NOT NULL,
  `rolename` varchar(50) NOT NULL,
  `roleinfo` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`roleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

DROP TABLE IF EXISTS `summertestdb`.`userinfo`;
CREATE TABLE  `summertestdb`.`userinfo` (
  `userID` varchar(50) NOT NULL,
  `name` varchar(50) NOT NULL,
  `gender` tinyint(1) DEFAULT '1',
  `birthday` date NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

DROP TABLE IF EXISTS `summertestdb`.`test`;
CREATE TABLE  `summertestdb`.`test` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `userid` varchar(50) DEFAULT NULL,
  `roleid` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `roleid` (`roleid`),
  KEY `userid` (`userid`),
  CONSTRAINT `roleid` FOREIGN KEY (`roleid`) REFERENCES `role` (`roleID`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `userid` FOREIGN KEY (`userid`) REFERENCES `userinfo` (`userID`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  以上脚本建立的过程都是没有问题的。

之后建立另一个包含外键约束的数据表：

DROP TABLE IF EXISTS `summertestdb`.`moudlerole`;
CREATE TABLE  `summertestdb`.`moudlerole` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `moudleid` varchar(50) DEFAULT NULL,
  `roleid` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `roleid` (`roleid`),
  KEY `moudleid` (`userid`),
  CONSTRAINT `roleid` FOREIGN KEY (`moudleid`) REFERENCES `role` (`roleID`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `userid` FOREIGN KEY (`userid`) REFERENCES `userinfo` (`userID`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 此时数据库报出MySQL ERROR 1005: Can't create table (errno: 150)错误。一般而言导致这种问题主要有种原因：

1、外键的引用类型不一样，例如主键是int外键是char

2、找不到主表中引用的列

3、主键和外键的字符编码不一致，

 

但由上面的脚本可以看出这三个问题都不会出现，这也只能是其他原因了。后来为了调试将建立userrole表的脚本修改：

DROP TABLE IF EXISTS `summertestdb`.`test`;
CREATE TABLE  `summertestdb`.`test` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `userid` varchar(50) DEFAULT NULL,
  `roleid` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `roleid` (`roleid`),
  KEY `userid` (`userid`),
  CONSTRAINT `roleid` FOREIGN KEY (`roleid`) REFERENCES `role` (`roleID`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `userid` FOREIGN KEY (`userid`) REFERENCES `userinfo` (`userID`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

又脚本可以看出，只是修改了表名，其他均为做该表，此时数据库依然报出相同的错误，而将外键约束删除之后则可以顺利执行，由此可见最有可能出现问题的地方就在外键约束之后。那就修改外键名称吧。

DROP TABLE IF EXISTS `summertestdb`.`test`;
CREATE TABLE  `summertestdb`.`test` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `userid` varchar(50) DEFAULT NULL,
  `roleid` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `roleid` (`roleid`),
  KEY `userid` (`userid`),
  CONSTRAINT `roleid` FOREIGN KEY (`FK1`) REFERENCES `role` (`roleID`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `userid` FOREIGN KEY (`FK2`) REFERENCES `userinfo` (`userID`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 这次顺利执行，问题解决。

 

PS:

 

时常会在定义Foreign Key的过程中出现 on delete set Null的情况，这个时候如果在数据定义中出现Not null约束条件也会出现这种错误。