Problem description
RDS for MySQL supports foreign key constraints. However, the following error message appears when you create a foreign key constraint.
Cannot add foreign key constraint
Cause
The field to be joined is not a primary key in the table to be joined.
Solution
The following takes a tstudent table and a tscoretable as an example to describe how to resolve this issue.
1. Run the following SQL statement to view the tstudent table structure and determine whether the field to be associated is a primary key field in the associated table.
show create table tstudent;
If a similar output is displayed, confirm that the tstudent table does not have a primary key.
2. Run the following SQL statement to confirm that the tscore table has a normal structure:
show create table tscore;
The following command output is returned.
3. Run the following SQL statement to add a primary key for the tstudent table:
alter table tstudent add primary key(sno);
4. Run the following SQL statement to create a foreign key constraint.
alter table tscore add constraint fk_tscore_sno foreign key(sno) references tstudent(sno);