ORA-00600 error reported in foreign key addition

This topic describes how to handle the ORA-00600 error 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 [SYS]> ALTER TABLE cust ADD CONSTRAINT C_FK FOREIGN KEY (c_w_id) REFERENCES ware(w_name);
ORA-00600: internal error code, arguments: -5317, Cannot add foreign key constraint

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

• Error code: ORA-00600

• Error code in OceanBase Database: 5317

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.

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 current table and the ware table, and determine whether the referenced column is the primary key or unique key. A column referenced by a foreign key must be the unique key or primary key column of the referenced table.

   obclient> DESC CUST;
   +--------+------------+------+-----+---------+-------+
   | Field  | Type       | Null | Key | Default | Extra |
   +--------+------------+------+-----+---------+-------+
   | C_ID   | NUMBER(38) | NO   | PRI | NULL    |       |
   | C_W_ID | NUMBER(38) | YES  |     | NULL    |       |
   +--------+------------+------+-----+---------+-------+
   2 rows in set
   
   obclient [SYS]> DESC WARE;
   +--------+---------------+------+-----+---------+-------+
   | Field  | Type          | Null | Key | Default | Extra |
   +--------+---------------+------+-----+---------+-------+
   | W_ID   | NUMBER(38)    | NO   | PRI | NULL    |       |
   | W_NAME | VARCHAR2(256) | YES  | UNI | NULL    |       |
   | W_CITY | VARCHAR2(256) | YES  | UNI | NULL    |       |
   +--------+--------------+------+-----+---------+-------+
   3 rows in set
   

2. Select the primary key and create a FOREIGN KEY constraint again.

   obclient [SYS]> ALTER TABLE CUST ADD CONSTRAINT C_FK FOREIGN KEY (c_w_id) REFERENCES WARE(w_id);
   Query OK, 0 rows affected