Oracle Error ORA-00001

Error code

ORA-00001

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

Constraints & Integrity

Last reviewed

24 October 2026

What it means

ORA-00001 means an INSERT, UPDATE or MERGE would create a row whose key already exists in a primary key, unique constraint or unique index, so Oracle rejected the statement.

A primary key, unique constraint or unique index allows each key value only once. When a statement would store a second row with the same key, Oracle rolls back that statement and raises ORA-00001. The message names the constraint as schema.name; in current releases it also names the table and the key columns, while 19c shows only the constraint name.

A constraint created without a name gets a system name such as SYS_C008123, which tells you nothing by itself: look it up in the data dictionary (below) to find its table and columns. Oracle's reference adds that more detail about the duplicate values is reported when the ERROR_MESSAGE_DETAILS parameter is on, which it says is the default in current releases.

Common causes

• The same row is inserted twice: a retried request, a double submit or a load that is run again.
• A sequence or identity column is behind the data, typically after rows were loaded with explicit ids, so the next generated value already exists.
• An UPDATE changes a key column, such as an email address, to a value another row already has.
• A MERGE matches on different columns from the unique key, so its INSERT branch inserts a key that already exists.
• A bulk load or INSERT ... SELECT brings in a source with duplicates, among its own rows or against existing rows.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT c.table_name, c.constraint_type, cc.column_name, cc.position
FROM all_constraints c
JOIN all_cons_columns cc
  ON cc.owner = c.owner AND cc.constraint_name = c.constraint_name
WHERE c.owner = 'SHOP'
  AND c.constraint_name = 'CUSTOMERS_EMAIL_UK'
ORDER BY cc.position;

-- no rows? it is a unique index, not a constraint
SELECT table_name, column_name, column_position
FROM all_ind_columns
WHERE index_owner = 'SHOP'
  AND index_name = 'CUSTOMERS_EMAIL_UK'
ORDER BY column_position;

SELECT *
FROM shop.customers
WHERE email = 'sara@example.com';  -- the value you tried to write

SELECT MAX(id) AS highest_id FROM shop.customers;

-- identity column: its sequence
SELECT column_name, sequence_name
FROM all_tab_identity_cols
WHERE owner = 'SHOP' AND table_name = 'CUSTOMERS';

SELECT sequence_name, last_number, increment_by
FROM all_sequences
WHERE sequence_owner = 'SHOP';

SELECT email, COUNT(*) AS copies
FROM shop.customers_staging
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

MERGE INTO shop.customers c
USING (SELECT 'sara@example.com' AS email, 'Sara Ahmed' 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);

INSERT INTO shop.customers (email, name)
SELECT s.email, s.name
FROM shop.customers_staging s
WHERE NOT EXISTS (SELECT 1 FROM shop.customers c WHERE c.email = s.email);

ALTER TABLE shop.customers
  MODIFY id GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE);

Examples

Inserting an email address that is already registered

Not reproduced here: no Oracle Database is installed for this site. The result shows the message format documented in Oracle's error reference for current releases, with this example's names filled in (the table is assumed to be in schema SHOP). Oracle Database 19c prints only the first part, up to "violated".

CREATE TABLE customers (
    id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email VARCHAR2(200) CONSTRAINT customers_email_uk UNIQUE,
    name  VARCHAR2(100)
);

INSERT INTO customers (email, name) VALUES ('sara@example.com', 'Sara');
INSERT INTO customers (email, name) VALUES ('sara@example.com', 'Sara B');

ORA-00001: unique constraint (SHOP.CUSTOMERS_EMAIL_UK) violated on table SHOP.CUSTOMERS columns (EMAIL)

An identity column that is behind the data

Not reproduced here: no Oracle Database is installed for this site. The constraint name is illustrative: a primary key declared without a name gets a system-generated SYS_C name, which differs on every database.

-- rows were loaded with explicit ids, for example from another system
INSERT INTO customers (id, email, name) VALUES (1, 'sara@example.com', 'Sara');

-- the identity column still starts at 1
INSERT INTO customers (email, name) VALUES ('ali@example.com', 'Ali');

ORA-00001: unique constraint (SHOP.SYS_C008123) violated on table SHOP.CUSTOMERS columns (ID)

How to prevent it

Name your constraints (CONSTRAINT customers_email_uk UNIQUE) so the error message tells you which rule fired. Let Oracle generate surrogate keys with identity columns or sequences, and re-align them after any load with explicit ids. Make loads that may run twice idempotent with MERGE or NOT EXISTS.

Keep the unique constraint even when the application checks first, and handle ORA-00001 in your code so a duplicate becomes a clear message such as "this email is already registered".

Common questions

Learn more on CodeWithSQL

• SQL constraints explained
• Primary key vs foreign key

Official documentation

• ORA-00001 (Oracle Database Error Messages)
• MERGE (Oracle SQL Language Reference 19c)
• Constraint clause (Oracle SQL Language Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.