Unique key conflict

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

Error: ERROR 1062

Error code

• Error code in OceanBase Database: 5024

• MySQL error code: 1062

Example

When you insert data into the dws_ny table, this error is returned, indicating that the primary key value 3 is duplicated with an existing one.

obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

Troubleshooting procedure

Method 1

If you use INSERT IGNORE INTO to avoid constraint conflicts, the IGNORE keyword can be used to ignore the impact of an insert failure caused by a constraint conflict.

1. Add the IGNORE keyword when you insert data into the t_insert table.

   obclient> INSERT IGNORE INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004);
   Query OK, 1 row affected
   

2. Query data in the t_insert table again.

   obclient> select * from t_insert;
   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 1970-01-01 16:05:45 |
   |  2 | US   | 10002 | 1970-01-01 16:05:54 |
   |  3 | EN   | 10003 | 1970-01-01 16:05:54 |
   |  4 | JP   | 10004 | 1970-01-01 16:06:08 |
   +----+------+-------+---------------------+
   4 rows in set
   

Method 2

If you use INSERT INTO ON DUPLICATE KEY UPDATE to avoid constraint conflicts, you can specify the action to take on duplicate primary keys or unique keys. In the following example, the conflicting content is updated.

Note

• With ON DUPLICATE KEY UPDATE specified, when the primary key or unique key to be inserted is duplicated with an existing one, the value to be inserted is replaced with the specified value.
• With ON DUPLICATE KEY UPDATE unspecified, when the primary key or unique key is duplicated with an existing one, an insert error is reported.

1. Execute the INSERT INTO ON DUPLICATE KEY UPDATE statement to avoid constraint conflicts when you insert data into the t_insert table.

   obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004) ON DUPLICATE KEY UPDATE name=VALUES(name);
   Query OK, 1 row affected
   

2. Query data in the t_insert table again.

   obclient> select * from t_insert;
   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 1970-01-01 16:05:45 |
   |  2 | US   | 10002 | 1970-01-01 16:05:54 |
   |  3 | UK   | 10003 | 1970-01-01 16:05:54 |
   |  4 | JP   | 10004 | 1970-01-01 16:06:08 |
   +----+------+-------+---------------------+
   4 rows in set