MySQL添加外键时报错 ERROR 1215 (HY000): Cannot add foreign key constraint
1.数据类型 2.数据表的引擎
数据表
mysql> show tables;+------------------+| Tables_in_market |+------------------+| customers_info|| orders|+------------------+2 rows in set (0.00 sec)
遇到错误信息
mysql> alter table orders add constraint fk_orders foreign key(c_id) references customers_info(c_num);ERROR 1215 (HY000): Cannot add foreign key constraint
首先想到可能类型不同，于是查看表结构
mysql> desc customers_info;+----------+------------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+----------+------------------+------+-----+---------+-------+| c_num| int(10) unsigned | NO| PRI | 0||| c_name| varchar(70) | YES | | NULL||| c_birth | datetime | NO| | NULL||| c_phone | varchar(50) | YES | | NULL||| c_gender | char(1) | YES | | NULL||+----------+------------------+------+-----+---------+-------+5 rows in set (0.02 sec)mysql> desc orders;+--------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+----------------+| o_num | int(11) | NO| PRI | NULL| auto_increment || o_date | date | YES | | NULL||| c_id| int(10) unsigned | YES | | NULL||+--------+------------------+------+-----+---------+----------------+3 rows in set (0.01 sec)
发现c_id和c_num类型是相同的，那就有可能是引擎出问题了，查看创建数据表时的引擎
mysql> show create table customers_info\G;*************************** 1. row ***************************Table: customers_infoCreate Table: CREATE TABLE `customers_info` ( `c_num` int(10) unsigned NOT NULL DEFAULT '0', `c_name` varchar(70) DEFAULT NULL, `c_birth` datetime NOT NULL, `c_phone` varchar(50) DEFAULT NULL, `c_gender` char(1) DEFAULT NULL, PRIMARY KEY (`c_num`)) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR:No query specifiedmysql> show create table orders\G;*************************** 1. row ***************************Table: ordersCreate Table: CREATE TABLE `orders` ( `o_num` int(11) NOT NULL AUTO_INCREMENT, `o_date` date DEFAULT NULL, `c_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`o_num`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR:No query specified
发现customers_info表的引擎是MyISAM, 修改此表的引擎为InnoDB
mysql> alter table customers_info engine = innoDB;Query OK, 0 rows affected (0.57 sec)Records: 0 Duplicates: 0 Waings: 0mysql> alter table orders add constraint fk_orders foreign key(c_id) references customers_info(c_num);Query OK, 0 rows affected (0.62 sec)Records: 0 Duplicates: 0 Waings: 0
返回Query OK表明外键建立成功了，查看一下
mysql> show create table orders\G;*************************** 1. row ***************************Table: ordersCreate Table: CREATE TABLE `orders` ( `o_num` int(11) NOT NULL AUTO_INCREMENT, `o_date` date DEFAULT NULL, `c_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`o_num`), KEY `fk_orders` (`c_id`), CONSTRAINT `fk_orders` FOREIGN KEY (`c_id`) REFERENCES `customers_info` (`c_num`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
作者：shootercheng
来源链接：https://www.cnblogs.com/shootercheng/p/6063123.html
版权声明：
1、JavaClub（https://www.javaclub.cn）以学习交流为目的，由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容，包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网，本站不享有版权，版权归原作者所有。
2、本站提供的内容仅用于个人学习、研究或欣赏，以及其他非商业性或非盈利性用途，但同时应遵守著作权法及其他相关法律的规定，不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容，请及时通知本站（javaclubcn@163.com），我们将第一时间核实后及时予以删除。
