mysql error :ERROR 1005 (HY000): Can't create table xxx

 

在建外键时出现一条错误：

ALTER">[e-mail]>ALTER TABLE `service` ADD CONSTRAINT `host_id_refs_id_507efddd` FOREIGN KEY (`host_id`) REFERENCES `host` (`id`);
ERROR 1005 (HY000): Can't create table 'site.#sql-c4e_c' (errno: 150)

[root@taohx vm129 site]# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
[root@taohx vm129 site]# perror 1005
Illegal error code: 1005

show">[e-mail]>show create table host;

CREATE TABLE `host` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cmdb_sn` varchar(32) NOT NULL,
  `internal_ip` varchar(16) NOT NULL,
  `external_ip` varchar(16) NOT NULL,
  `idc_name` varchar(16) NOT NULL,
  `status` enum('online','unused','repair') DEFAULT 'unused',
  `description` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

CREATE TABLE `service` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `host_id` int(11) unsigned NOT NULL,
  `port` int(10) unsigned NOT NULL,
  `role` enum('master','mb','slave','relay') DEFAULT 'slave',
  `status` enum('online','offline','unused') DEFAULT 'unused',
  `description` varchar(255) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `host_id` (`host_id`,`port`),
  KEY `idx_port` (`port`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

经查阅资料：

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

原因是关联字段类型不匹配,要完全匹配才行。

alter">[e-mail]>alter table host change id id int(11) unsigned not null auto_increment;         
Query OK, 11 rows affected (0.06 sec)
Records: 11  Duplicates: 0  Warnings: 0

ALTER">[e-mail]>ALTER TABLE `service` ADD CONSTRAINT `host_id_refs_id_507efddd` FOREIGN KEY (`host_id`) REFERENCES `host` (`id`);
Query OK, 10 rows affected (0.05 sec)