ORA-00001: unique constraint violated

ORA-00001: unique constraint (SCHEMA.CONSTRAINT_NAME) violated

An INSERT, UPDATE or MERGE tried to store a value that already exists in a column protected by a primary key or unique constraint. Find the constraint named in the message, see which columns it covers, and either change the value or update the existing row instead.

What it means

A primary key or unique constraint (or a unique index) guarantees that no two rows share the same value in a set of columns. When a statement would create a duplicate, Oracle rejects that statement and names the constraint in the message — for example HR.EMP_EMAIL_UK. Rows changed earlier in the same transaction are not affected.

Common causes

• Inserting a row whose key already exists — often the same data loaded twice.
• A sequence that is behind the data, typically after an import or a manual insert with hard-coded IDs.
• An UPDATE that sets a unique column to a value another row already has.
• Parallel sessions inserting the same natural key at the same time.

How to fix it

1. Read the constraint name in the message and look up its columns in USER_CONS_COLUMNS (or ALL_CONS_COLUMNS with the owner).
2. Check whether the row already exists; if it does, update it instead — MERGE does both in one statement.
3. If a sequence is behind, move it past the current maximum: ALTER SEQUENCE … RESTART START WITH n on Oracle 18c and later, or increment it until it passes the maximum on older releases.
4. For bulk loads, use DML error logging (LOG ERRORS INTO) to collect duplicates instead of failing the whole load.

Example

Find the columns behind the constraint:

SELECT column_name, position
  FROM all_cons_columns
 WHERE owner = 'HR'
   AND constraint_name = 'EMP_EMAIL_UK'
 ORDER BY position;

Insert or update in one statement:

MERGE INTO employees e
USING (SELECT :email AS email, :name AS name FROM dual) s
   ON (e.email = s.email)
 WHEN MATCHED THEN UPDATE SET e.name = s.name
 WHEN NOT MATCHED THEN INSERT (email, name) VALUES (s.email, s.name);

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Related errors

ORA-01400ORA-12899ORA-00054 All Oracle errors →

Oracle documentation for ORA-00001. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.