How to Fix ORA-00001: Unique Constraint Violated in Oracle

ORA-00001 means an INSERT, UPDATE, or MERGE tried to store a value that a primary key or unique constraint already holds. The database refuses the whole statement, so no row is added or changed. The fix is never to drop the constraint: find which column and value collided, then either skip the duplicate, update the existing row, or correct the data that produced it.

Reproduce the Error

A table with a primary key on customer_id and a unique constraint on email gets one customer, then two rows that repeat a value:

Example:

create table demo_customers (
  customer_id number       constraint demo_customers_pk primary key,
  email       varchar2(80) constraint demo_customers_email_uk unique
);
insert into demo_customers values (1, 'ana@example.com');
commit;
insert into demo_customers values (2, 'ana@example.com');
insert into demo_customers values (1, 'raj@example.com');

Output:

Table DEMO_CUSTOMERS created.

1 row inserted.

Commit complete.

Error starting at line : 7
In command -
insert into demo_customers values (2, 'ana@example.com')
Error report -
ORA-00001: unique constraint (NIMBUS.DEMO_CUSTOMERS_EMAIL_UK) violated on table
NIMBUS.DEMO_CUSTOMERS columns (EMAIL)
ORA-03301: (ORA-00001 details) row with column values (EMAIL:'ana@example.com')
already exists

Error starting at line : 8
In command -
insert into demo_customers values (1, 'raj@example.com')
Error report -
ORA-00001: unique constraint (NIMBUS.DEMO_CUSTOMERS_PK) violated on table
NIMBUS.DEMO_CUSTOMERS columns (CUSTOMER_ID)
ORA-03301: (ORA-00001 details) row with column values (CUSTOMER_ID:1) already
exists

In Oracle AI Database 26ai the message names the constraint, the table, the columns, and, in the ORA-03301 detail line, the value that already exists. Older releases show only the constraint name, such as (NIMBUS.DEMO_CUSTOMERS_EMAIL_UK).

Find the Constraint's Columns

When the message shows only the constraint name, the data dictionary tells which columns it covers:

Example:

-- which columns does the constraint in the message cover?
select constraint_name, constraint_type, column_name
from   user_constraints join user_cons_columns using (constraint_name, table_name)
where  constraint_name = 'DEMO_CUSTOMERS_EMAIL_UK';

Output:

CONSTRAINT_NAME            CONSTRAINT_TYPE    COLUMN_NAME
__________________________ __________________ ______________
DEMO_CUSTOMERS_EMAIL_UK    U                  EMAIL

Constraint type U is a unique constraint and P a primary key. Name constraints when you create them, as here, so the message alone says what was violated.

Fix: Insert Only What Is New

MERGE adds the row only when no row with that email exists, and INSERT ... SELECT with NOT EXISTS does the same in plain SQL:

Example:

-- skip rows that already exist, or update them instead
merge into demo_customers c
using (select 2 as customer_id, 'ana@example.com' as email from dual) n
on    (c.email = n.email)
when not matched then insert (customer_id, email) values (n.customer_id, n.email);

insert into demo_customers (customer_id, email)
select 3, 'raj@example.com' from dual
where  not exists (select 1 from demo_customers where email = 'raj@example.com');

select * from demo_customers order by customer_id;

Output:

0 rows merged.

1 row inserted.

   CUSTOMER_ID EMAIL
______________ __________________
             1 ana@example.com
             3 raj@example.com

The MERGE found ana@example.com already present and merged 0 rows, so no duplicate was attempted; the INSERT added the new customer 3.

Other Common Causes

• A sequence or identity column behind its data: rows were loaded with explicit IDs, and the sequence now generates values that already exist. Move the sequence past the highest ID.
• Running the same load script twice. Make loads repeatable with MERGE, or check for existing rows first.
• Two sessions inserting the same key at the same time: the second one fails when the first commits. Catch DUP_VAL_ON_INDEX in PL/SQL and handle it.

Things to Know

• In PL/SQL, ORA-00001 is the predefined exception DUP_VAL_ON_INDEX.
• For bulk loads, DML error logging (LOG ERRORS) records the duplicate rows in a table instead of failing the whole statement.
• A unique constraint allows any number of rows whose key columns are all NULL.

Related Guides

• How to Log DML Errors in Oracle
• How to Query Constraints from the Data Dictionary

Conclusion

ORA-00001 means a value already exists in a column protected by a primary key or unique constraint. Read the constraint, columns, and value from the message, then insert only new rows with MERGE or NOT EXISTS, update existing ones, or fix the sequence that produced the duplicate.