ORA-00001 Unique Constraint Violated Solution

Have you gotten an “ORA-00001 unique constraint violated” error? Learn what has caused it and how to resolve it in this article.

Table of Contents

ORA-00001 Cause

If you’ve tried to run an INSERT or UPDATE statement, you might have gotten this error:

ORA-00001 unique constraint violated

This has happened because the INSERT or UPDATE statement has created a duplicate value in a field that has either a PRIMARY KEY constraint or a UNIQUE constraint.

ORA-00001 Solution

There are a few solutions to the “ORA-00001 unique constraint violated” error:

Change your SQL so that the unique constraint is not violated.
Change the constraint to allow for duplicate values
Drop the constraint from the column.
Disable the unique constraint.

Solution 1: Modify your SQL

You can modify your SQL to ensure you’re not inserting a duplicate value.

If you’re using ID values for a primary key, it’s a good idea to use a sequence to generate these values. This way they are always unique.

You can use the sequence.nextval command to get the next value of the sequence.

So, instead of a query like this, which may not work if the employee_id value is already used:

INSERT INTO employee (employee_id, first_name, last_name)
VALUES (231, 'John', 'Smith');

You can use this:

INSERT INTO employee (employee_id, first_name, last_name)
VALUES (seq_emp_id.nextval, 'John', 'Smith');

Assuming the sequence is set up correctly, this should ensure that a unique value is used.’

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

Find the constraint that was violated

The “ORA-00001 unique constraint violated” error usually shows a name of a constraint. This could be a descriptive name (if you’ve named your constraints when you create them) or a random-looking name for a constraint.

You can query the all_indexes view to find the name of the table and other information about the constraint:

SELECT *
FROM all_indexes
WHERE index_name = <constraint_name>;

This will give you more information about the specific fields and the table.

Solution 2: Change the constraint to allow for duplicates

If you have a unique constraint or primary key set up on your table, you could change the constraint to allow for duplicate values, to get around the ORA-00001 error.

Let’s say the unique constraint applies to first_name and last_name, which means the combination of those fields must be unique.

If you find that that rule is incorrect, you can change the constraint to say that the combination of first_name, last_name, and date_of_birth must be unique.

To do this, you need to drop and recreate the constraint.

To drop the constraint:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Then, recreate the constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (col1, col2....);

Now your constraint will reflect your rules.

Solution 3: Remove the unique constraint

The third solution would be to drop the unique constraint altogether.

This should only be done if it is not required.

To do this, run the ALTER TABLE command:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

The constraint will be removed and you should be able to UPDATE or INSERT the data successfully.

Solution 4: Disable the unique constraint

The final solution could be useful if you’re doing a lot of data manipulation and you need to temporarily disable the constraint, with the aim of enabling it later.

Disabling the constraint will leave it in the data dictionary and on the table, with the same name, it just won’t be checked when data is inserted or updated.

To disable the constraint:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

If you need to enable the constraint in the future:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

So, that’s how you can resolve the “ORA-00001 unique constraint violated” error.