Mysql ERROR 1005: Can’t create Table (errno: 150) when creating a reference constraint in mysql results in an ERROR message that cannot create a reference constraint.
A rough picture of what went wrong
1, foreign key reference type is different, such as the primary key is int foreign key is char
2. The column referenced in the main table could not be found
3, the primary key and foreign key character encoding is not consistent, may also be different storage engineThe problem I ran into was that none of the above was true, but it did succeed after removing a foreign key constraint. One thing is for sure, this error was caused by a foreign key constraint.
To summarize the experience:
In fact, in a MySQL database, if there are more than one database, it is better to write SQL with the name of the database, such as:
CREATE TABLE `testdb`.`user_visitor_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(45) COLLATE utf8_bin DEFAULT NULL COMMENT 'id',
`user_ip` varchar(15) COLLATE utf8_bin DEFAULT NULL COMMENT 'ip',
`refer_url` varchar(45) COLLATE utf8_bin DEFAULT NULL COMMENT 'source',
`visit_time` timestamp NULL DEFAULT NULL COMMENT 'time',
`leave_time` timestamp NULL DEFAULT NULL COMMENT 'leave time',
`module_name` varchar(45) COLLATE utf8_bin DEFAULT NULL COMMENT 'moudle name',
`news_id` int(11) DEFAULT NULL COMMENT 'Access to information on the information page id',
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `seoID_idx` (`seo_id`),
CONSTRAINT `seo_msg_id` FOREIGN KEY (`seo_id`) REFERENCES `college_seo_msg` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User access log';
