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 OceanBase Database in MySQL mode.

Symptom

When you create a FOREIGN KEY constraint on the cust table, this error is returned, 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

• MySQL error code: 1215

For more information about the error codes, see Error code overview.

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, if the column of the referenced 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 keys of the associated tables.

   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. Create the FOREIGN KEY constraint for 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