Duplicate data inserted, with Error ORA-00001 reported

This topic describes how to handle Error ORA-00001, where duplicate data is inserted into the Oracle mode of OceanBase Database.

Symptom

When you insert data into the t_insert table with a primary key, this error is returned, indicating that a primary key value in the inserted data is duplicate with an existing primary key value.

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

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

• Error code: ORA-00001

• Error code in OceanBase Database: 5024

• SQLSTATE: 23000

For more information about the error codes, see Overview of error codes.

Possible causes

When you insert data into a table with a UNIQUE constraint, the system reports an error when the inserted data is duplicate with an existing record.

Troubleshooting procedure

You can take either of the following two methods to solve the conflict.

1. Query data in the t_insert table to check whether the inserted data is duplicate with the data in the primary key column, and determine whether to retain or change the existing row.

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

2. If you want to retain the existing row, execute the following statement to update the row where id is 3.

   obclient [SYS]> UPDATE t_insert 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 Replace data.
   • If you do not want to retain the existing row, you can delete the row as needed. For more information, see Delete data.

3. After the operation is successful, view the data in the t_insert table again.

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