ERROR 1215 reported in foreign key addition

This topic describes how to handle ERROR 1215 reported when you add a foreign key to a table in the MySQL mode of OceanBase Database.

Symptom

When you create a FOREIGN KEY constraint on the cust table, this error is reported, indicating that the referenced column is incorrect.

obclient> ALTER TABLE cust ADD CONSTRAINT c_fk FOREIGN KEY (c_w_id) REFERENCES ware(w_name);
ERROR 1215 (HY000): Cannot add foreign key constraint

The error codes corresponding to this error message are as follows:

• Error code: ERROR 1215

• Error code in OceanBase Database: 5317

• Error code compatible with MySQL Database: 1215

For more information about the error codes, see Overview of error messages.

Possible causes

• When you add a FOREIGN KEY constraint, if the type of the foreign key field does not match that of the associated field, this error is reported.

• When you add a FOREIGN KEY constraint, if the referenced column in the parent table is not a primary key or unique index, this error is reported.

Troubleshooting procedure

Take the following steps to view the type of each field in the current table and the associated table, confirm the primary key of the associated table, and then re-create a FOREIGN KEY constraint.

1. View the schemas of the ware and cust tables and find the primary key of the ware table.

   obclient> DESC ware;
   +--------+--------------+------+-----+---------+-------+
   | Field  | Type         | Null | Key | Default | Extra |
   +--------+--------------+------+-----+---------+-------+
   | w_id   | int(11)      | NO   | PRI | NULL    |       |
   | w_name | varchar(256) | YES  | UNI | NULL    |       |
   | w_city | varchar(256) | YES  | UNI | NULL    |       |
   +--------+--------------+------+-----+---------+-------+
   3 rows in set
   
   obclient> DESC cust;
   +--------+---------+------+-----+---------+-------+
   | Field  | Type    | Null | Key | Default | Extra |
   +--------+---------+------+-----+---------+-------+
   | c_id   | int(11) | NO   | PRI | NULL    |       |
   | c_w_id | int(11) | YES  |     | NULL    |       |
   +--------+---------+------+-----+---------+-------+
   2 rows in set
   

2. Add the FOREIGN KEY constraint to the cust table again.

   obclient> ALTER TABLE cust ADD CONSTRAINT c_fk FOREIGN KEY (c_w_id) REFERENCES ware(w_id);
   Query OK, 0 rows affected