// Oracle error reference

ORA-00001: unique constraint violated

An INSERT or UPDATE would duplicate a value in a primary key or unique constraint or index. The message names the constraint as SCHEMA.NAME.

Error code
ORA-00001
Category
Constraints and keys
Error message
unique constraint (string.string) violated

What it means

ORA-00001 means the statement would create a second row with the same key in a primary key, unique constraint or unique index. The message names the constraint, for example ORA-00001: unique constraint (SALES.ORDERS_PK) violated; look it up to see its columns.

Newer releases also name the table and columns: unique constraint (constraint_schema.constraint_name) violated on table table_schema.table_name columns (column_names).

Common causes

  • Inserting a row whose key already exists, for example re-running a load.
  • A sequence that is behind the data, so NEXTVAL produces an id already in the table, typically after data was imported with explicit ids.
  • Two sessions inserting the same key at once; the second waits and then fails if the first commits.
  • An UPDATE that sets a unique column to a value another row has.

How to fix it

  1. Find the columns behind the constraint named in the message.

    SELECT table_name, column_name, position
    FROM all_cons_columns
    WHERE owner = 'SALES' AND constraint_name = 'ORDERS_PK'
    ORDER BY position;
  2. Insert or update in one statement with MERGE.

    MERGE INTO customers c
    USING (SELECT '[email protected]' AS email, 'Ada' AS name FROM dual) s
    ON (c.email = s.email)
    WHEN MATCHED THEN UPDATE SET c.name = s.name
    WHEN NOT MATCHED THEN INSERT (email, name) VALUES (s.email, s.name);
  3. If a sequence is behind, move it past the current maximum (18c and later).

    SELECT MAX(id) FROM orders;
    ALTER SEQUENCE orders_seq RESTART START WITH 100001;
  4. To skip duplicates in a bulk insert, the IGNORE_ROW_ON_DUPKEY_INDEX hint or DML error logging (LOG ERRORS INTO) can set the bad rows aside.

Reproduce it safely

CREATE TABLE t (id NUMBER PRIMARY KEY);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (1);  -- ORA-00001: unique constraint (...) violated
DROP TABLE t PURGE;

Related errors

More constraints and keys errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.