ERROR 1005 (HY000):&nb…

最新推荐文章于 2023-07-02 22:24:35 发布

Hank丶Yoon

最新推荐文章于 2023-07-02 22:24:35 发布

阅读量412

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/yoon0205/article/details/50605577

版权

MySQL 专栏收录该内容

88 篇文章 0 订阅

订阅专栏

CREATE TABLE `user` (
`id` bigint(32) NOT NULL AUTO_INCREMENT ,
`name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`erp` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`email` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`department` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`position` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`role` int(1) NOT NULL ,
`lastProductID` bigint(32) NULL DEFAULT NULL ,
`userIP` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`debugRunStatus` int(1) NULL DEFAULT NULL ,
`createTime` datetime NULL DEFAULT NULL ,
`remark` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`ho1Name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`ho2Name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`ho3Name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`ho4Name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`ho5Name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`modifyTime` datetime NULL DEFAULT NULL ,
`defaultType` int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`id`, `erp`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=486
;

CREATE TABLE `system_log` (
`id` bigint(32) NOT NULL AUTO_INCREMENT ,
`erp` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`operType` int(11) NOT NULL DEFAULT 0 COMMENT '1 ---登录' ,
`operTime` datetime NOT NULL ,
`remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`browerIP` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`modifyTime` datetime NOT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`erp`) REFERENCES `user` (`erp`) ON DELETE CASCADE ON UPDATE RESTRICT
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2247
;

报错信息如下：

ERROR 1005 (HY000): Can't create table 'matrix.system_log' (errno: 150)

mysql> show engine innodb status\G

140303 13:58:42 Error in foreign key constraint of table matrix/system_log:
FOREIGN KEY (`erp`) REFERENCES `user` (`erp`) ON DELETE CASCADE ON UPDATE RESTRICT
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2247:
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.

报错原因有如下几种原因：
1.字段类型不一致
2.引用的字段和被引用的字段“NULL”属性不一致
3.为引用的表没有创建索引

上述报错是因为没有为引用的字段创建索引。