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

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

ALTER">mysql@127.0.0.1.site>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

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

经查阅资料：

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

alter">mysql@127.0.0.1.site>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">mysql@127.0.0.1.site>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)