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 ORA-00001 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 of ORA-00001
- 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 ORA-00001
- Read the constraint name in the message and look up its columns in
USER_CONS_COLUMNS(orALL_CONS_COLUMNSwith the owner). - Check whether the row already exists; if it does, update it instead —
MERGEdoes both in one statement. - If a sequence is behind, move it past the current maximum:
ALTER SEQUENCE … RESTART START WITH non Oracle 18c and later, or increment it until it passes the maximum on older releases. - For bulk loads, use DML error logging (
LOG ERRORS INTO) to collect duplicates instead of failing the whole load.
ORA-00001 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.