Foreign key conflict

This topic describes errors related to foreign key conflicts and the troubleshooting procedure.

Error: ERROR 1215 (HY000): Cannot add foreign key constraint

Error code

• Error code in OceanBase Database: 5317

• MySQL error code: 1215

Example

When you create a foreign key constraint on the cust table, this error is returned, indicating that the referenced field is incorrect.

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

Troubleshooting procedure

1. View the schemas of the ware and cust tables and find their primary keys.

   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 again.

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