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
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
NEXTVALproduces 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
UPDATEthat sets a unique column to a value another row has.
How to fix it
-
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;
-
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);
-
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;
-
To skip duplicates in a bulk insert, the
IGNORE_ROW_ON_DUPKEY_INDEXhint 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
ORA-02291integrity constraint violated - no parent keyORA-01400cannot insert NULL into column23505PostgreSQL: duplicate key violates unique constraint2627SQL Server: Violation of PRIMARY KEY or UNIQUE KEY1062MySQL: Duplicate entry for keySQLITE_CONSTRAINT_UNIQUESQLite: UNIQUE constraint failed
More constraints and keys errors
ORA-02292integrity constraint violated - child record foundORA-02449unique/primary keys referenced by foreign keysORA-02266keys referenced by enabled foreign keys
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.