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.