Unique key conflict

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

Error: ORA-00001

Error code

• Error code in OceanBase Database: 5024

• SQLSTATE: 23000

Error message

ORA-00001: unique constraint 'US' for key 'UK_NAME' violated

Example

When you insert data into the dws_ny table, the system displays a message, indicating that the primary key value 3 is duplicated.

obclient> INSERT INTO dws_ny(id, name, value) VALUES (3,'US', 10003),(4, 'JP', 10004);
ORA-00001: unique constraint '3' for key 'PRIMARY' violated

Troubleshooting procedure

1. Query data in the dws_ny table to confirm whether the inserted data is duplicated with data in the primary key column and determine whether to retain or change the data in the existing rows.

   obclient> SELECT * FROM dws_ny;
   +----+------+-------+
   | ID | NAME | VALUE |
   +----+------+-------+
   |  1 | CN   | 10001 |
   |  2 | EN   | 10002 |
   |  3 | UK   | 10003 |
   |  4 | JP   | 10004 |
   +----+------+-------+
   4 rows in set
   

2. If necessary, you can retain the existing row and change the data in the column whose id = 3.

   obclient> UPDATE dws_ny SET name = 'US' WHERE id = 3;
   Query OK, 1 row affected
   

   Note

   • You can also use the MERGE INTO statement to avoid unique key conflicts. For more information, see About the MERGE statement
   • If you confirm that the existing row is not needed, you can delete it. For more information, see DELETE.

3. Query data in the dws_ny table again.

   obclient>  SELECT * FROM dws_ny;
   +----+------+-------+
   | ID | NAME | VALUE |
   +----+------+-------+
   |  1 | CN   | 10001 |
   |  2 | EN   | 10002 |
   |  3 | US   | 10003 |
   |  4 | JP   | 10004 |
   +----+------+-------+
   4 rows in set