Problem description
Apsaradb 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 tscore
table as an example to describe how to resolve this issue.
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.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.
Run the following SQL statement to add a primary key for the
tstudent
table:alter table tstudent add primary key(sno);
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);
Application scope
ApsaraDB RDS for MySQL