RDS for MySQL中创建外键约束提示Cannot add foreign key constraint
问题现象
RDS for MySQL是支持外键约束的，但在创建外键约束提示"Cannot add foreign key constraint"，会被误认为RDS不支持外键约束：
mysql>alter table tscore add constraint fk_tscore_sno foreign key(sno) references tstudent(sno);
Cannot add foreign key constraint
出现该错误的原因是要关联的表(如tstudent)在该列不是主键。
2. 解决方法
1. 先查看表结构判断是否关联表在对应列不是主键：
mysql>show create table tstudent;
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tstudent | CREATE TABLE `tstudent` (
`sno` varchar(4) NOT NULL DEFAULT '',
`sname` varchar(3) DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
共返回 1 行记录,花费 267.17 ms.
mysql>show create table tscore;
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tscore | CREATE TABLE `tscore` (
`sno` char(4) DEFAULT NULL,
`cno` char(3) DEFAULT NULL,
`score` int(10) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
共返回 1 行记录,花费 128.94 ms.
2. 为tstudent添加主键，如图：
mysql>alter table tstudent add primary key(sno);
执行成功,花费 124.90 ms.
3. 再次创建外键，即可成功创建，如图：
mysql>alter table tscore add constraint fk_tscore_sno foreign key(sno) references tstudent(sno);
执行成功,花费 161.55 ms.
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
