Resolve ORA-00001: unique constraint violated in Oracle
Description:
The ORA-00001 error occurred in an Oracle database that indicates a violation of a unique constraint.
This error occurs when a SQL statement tries to insert or update a record in a table, and the operation results in a duplicate value in a column with a unique constraint.
Cause:
This error is caused by attempting to insert or update a record with a value that already exists in a column defined with a unique constraint.
Resolved ORA-00001: unique constraint violated in Oracle
Solution
To resolve the ORA-00001 error, you can take the following steps:
Identify the duplicate value:
Determine which column is violating the unique constraint.
Identify the duplicate value causing the violation.
Review the unique constraint:
Check the unique constraint defined in the column.
Ensure that the constraint is correctly set up and reflects the intended uniqueness criteria.
Modify the data:
Update or delete the existing record with the duplicate value.
Alternatively, modify the new data being inserted or updated to avoid the duplicate.
Example:
Consider a table emp with a unique constraint on the emp_id column.
Attempting to insert a duplicate value:
To create a table with a unique constraint
CREATE TABLE emp (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50)
);
To insert a record with a duplicate emp_id
INSERT INTO emp VALUES (1, ‘John Doe’);
INSERT INTO emp VALUES (1, ‘Jane Smith’); —This will trigger ORA-00001
To resolve ORA-00001, either delete the existing record with emp_id = 1 or update the new data to have a different emp_id.
To delete the existing record
DELETE FROM emp WHERE emp_id = 1;
OR now update the new data
INSERT INTO emp VALUES (1, ‘Jane Smith’); —Now it won’t trigger ORA-00001
By ensuring that the unique constraint is maintained and handling duplicate values appropriately, you can resolve the ORA-00001 error.