Duplicate data inserted, with ORA-00001 error reported

This topic describes how to handle the ORA-00001 error, where duplicate data is entered during data insertion to OceanBase Database in Oracle mode.

Symptom

When you insert data into the t_insert table with primary keys, this error is returned, indicating that the primary key values in the inserted data are duplicate with the existing primary key values.

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 Error code overview.

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 confirm whether the inserted data is duplicate with data in the primary key column and determine whether to retain or change the data in the existing rows.

   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. To retain existing rows, you can execute the following statement to update the record whose id value 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 confirm that the existing row is not needed, you can delete it. For more information, see Delete data.

3. After successful deletion, 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