ORA-00001: unique constraint violated

ORA-00001: unique constraint (LAB_A.PRODUCTS_PK) violated on table LAB_A.PRODUCTS columns (PRODUCT_ID)

The row you tried to save has the same key value as a row that already exists.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-11

ORA-00001 is raised when an INSERT or UPDATE would create a second row with the same value in a primary key, unique constraint or unique index.

On recent versions (23ai / 26ai) the message tells you a lot: the constraint, the table, the columns, and a second line ORA-03301 with the exact value that already exists, e.g. (PRODUCT_ID:100). When that value is a small number like 1, suspect a sequence or identity column that is behind the data.

Causes and fixes

  1. The same key value is inserted twice
  2. The sequence is behind the existing data
  3. An identity column is behind rows inserted with explicit IDs
  4. A unique index (not a constraint) rejects the value

1The same key value is inserted twice

Product 100 already exists, and the INSERT tries to add it again with a new price. ORA-03301 shows the duplicate value: PRODUCT_ID:100.

First look at the existing row. If the intent was "insert, or update if it already exists", use MERGE: here it updated the existing row (1 row merged).

Setup
Setuprun as lab_a
CREATE TABLE products (product_id NUMBER CONSTRAINT products_pk PRIMARY KEY, name VARCHAR2(50), price NUMBER);
INSERT INTO products VALUES (100, 'Keyboard', 30);
COMMIT;
OUTPUT
Table created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO products VALUES (100, 'Keyboard', 35);
OUTPUT
INSERT INTO products VALUES (100, 'Keyboard', 35)
*
ERROR at line 1:
ORA-00001: unique constraint (LAB_A.PRODUCTS_PK) violated on table LAB_A.PRODUCTS columns (PRODUCT_ID)
ORA-03301: (ORA-00001 details) row with column values (PRODUCT_ID:100) already exists
Help: https://docs.oracle.com/error-help/db/ora-00001/
Checkrun as lab_a
SELECT * FROM products WHERE product_id = 100;
OUTPUT
PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
       100 Keyboard                               30

1 row selected.

If it really is a different item, give it a key that is not used yet.

After the fixrun as lab_a
MERGE INTO products p
USING (SELECT 100 AS product_id, 'Keyboard' AS name, 35 AS price FROM dual) s
   ON (p.product_id = s.product_id)
 WHEN MATCHED THEN UPDATE SET p.name = s.name, p.price = s.price
 WHEN NOT MATCHED THEN INSERT (product_id, name, price) VALUES (s.product_id, s.name, s.price);
COMMIT;
OUTPUT
1 row merged.


Commit complete.

2The sequence is behind the existing data

Rows 1 to 5 were loaded with explicit IDs (a migration or a copy from another system), but the sequence still starts at 1. The first NEXTVAL returns 1, which already exists: ORDER_ID:1.

Compare MAX(order_id) with the sequence and move the sequence past the maximum. On this database ALTER SEQUENCE ... RESTART START WITH does it in one statement.

Setup
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER CONSTRAINT orders_pk PRIMARY KEY, amount NUMBER);
INSERT INTO orders SELECT LEVEL, LEVEL * 100 FROM dual CONNECT BY LEVEL <= 5;
COMMIT;
CREATE SEQUENCE orders_seq;
OUTPUT
Table created.


5 rows created.


Commit complete.


Sequence created.
Reproducerun as lab_a
INSERT INTO orders VALUES (orders_seq.NEXTVAL, 700);
OUTPUT
INSERT INTO orders VALUES (orders_seq.NEXTVAL, 700)
*
ERROR at line 1:
ORA-00001: unique constraint (LAB_A.ORDERS_PK) violated on table LAB_A.ORDERS columns (ORDER_ID)
ORA-03301: (ORA-00001 details) row with column values (ORDER_ID:1) already exists
Help: https://docs.oracle.com/error-help/db/ora-00001/
Checkrun as lab_a
SELECT MAX(order_id) AS max_id FROM orders;
OUTPUT
    MAX_ID
----------
         5

1 row selected.
Fixrun as lab_a
ALTER SEQUENCE orders_seq RESTART START WITH 6;
OUTPUT
Sequence altered.

Use the value MAX + 1 from your own data. Here the maximum was 5, so the sequence restarts at 6.

After the fixrun as lab_a
INSERT INTO orders VALUES (orders_seq.NEXTVAL, 700);
OUTPUT
1 row created.

3An identity column is behind rows inserted with explicit IDs

GENERATED BY DEFAULT AS IDENTITY allows you to insert your own IDs. Rows 1 to 4 were inserted that way, so the identity generator still starts at 1 and the next automatic insert collides: TICKET_ID:1.

START WITH LIMIT VALUE moves the identity to just after the current maximum value in the column.

Setup
Setuprun as lab_a
CREATE TABLE tickets (ticket_id NUMBER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT tickets_pk PRIMARY KEY, title VARCHAR2(50));
INSERT INTO tickets (ticket_id, title) VALUES (1, 'migrated 1');
INSERT INTO tickets (ticket_id, title) VALUES (2, 'migrated 2');
INSERT INTO tickets (ticket_id, title) VALUES (3, 'migrated 3');
INSERT INTO tickets (ticket_id, title) VALUES (4, 'migrated 4');
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO tickets (title) VALUES ('new ticket');
OUTPUT
INSERT INTO tickets (title) VALUES ('new ticket')
*
ERROR at line 1:
ORA-00001: unique constraint (LAB_A.TICKETS_PK) violated on table LAB_A.TICKETS columns (TICKET_ID)
ORA-03301: (ORA-00001 details) row with column values (TICKET_ID:1) already exists
Help: https://docs.oracle.com/error-help/db/ora-00001/
Fixrun as lab_a
ALTER TABLE tickets MODIFY ticket_id GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE);
OUTPUT
Table altered.
After the fixrun as lab_a
INSERT INTO tickets (title) VALUES ('new ticket');
OUTPUT
1 row created.

4A unique index (not a constraint) rejects the value

The name in the message, USERS_EMAIL_UX, is not found in USER_CONSTRAINTS (no rows selected): it is a unique index. The column is shown as SYS_NC00003$ because the index is on an expression.

USER_IND_EXPRESSIONS shows the expression LOWER("EMAIL"): emails are compared case-insensitively, so Kim@Example.com duplicates the existing kim@example.com. Search with the same expression to find the existing row and use it instead of inserting a new one.

Setup
Setuprun as lab_a
CREATE TABLE users (user_id NUMBER PRIMARY KEY, email VARCHAR2(100));
CREATE UNIQUE INDEX users_email_ux ON users (LOWER(email));
INSERT INTO users VALUES (1, 'kim@example.com');
COMMIT;
OUTPUT
Table created.


Index created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO users VALUES (2, 'Kim@Example.com');
OUTPUT
INSERT INTO users VALUES (2, 'Kim@Example.com')
*
ERROR at line 1:
ORA-00001: unique constraint (LAB_A.USERS_EMAIL_UX) violated on table LAB_A.USERS columns (SYS_NC00003$)
ORA-03301: (ORA-00001 details) row with column values (SYS_NC00003$:'kim@example.com') already exists
Help: https://docs.oracle.com/error-help/db/ora-00001/
Checkrun as lab_a
SELECT constraint_name FROM user_constraints WHERE constraint_name = 'USERS_EMAIL_UX';
OUTPUT
no rows selected
Checkrun as lab_a
SELECT i.index_name, i.uniqueness, e.column_expression
  FROM user_indexes i JOIN user_ind_expressions e ON e.index_name = i.index_name
 WHERE i.index_name = 'USERS_EMAIL_UX';
OUTPUT
INDEX_NAME                     UNIQUENES COLUMN_EXPRESSION
------------------------------ --------- --------------------------------------------------------------------------------
USERS_EMAIL_UX                 UNIQUE    LOWER("EMAIL")

1 row selected.

For a normal (non-expression) unique index, USER_IND_COLUMNS lists the columns.

After the fixrun as lab_a
SELECT user_id, email FROM users WHERE LOWER(email) = LOWER('Kim@Example.com');
OUTPUT
   USER_ID EMAIL
---------- ----------------------------------------------------------------------------------------------------
         1 kim@example.com

1 row selected.

FAQ

How do I find which column and value caused ORA-00001?

On 23ai / 26ai, read the full message: it lists the table and columns, and ORA-03301 shows the duplicate value. On older versions only the constraint name is shown; look it up in USER_CONS_COLUMNS, or in USER_IND_COLUMNS if it is an index.

ORA-00001 on an INSERT that uses a sequence. Why?

The sequence is lower than the IDs already in the table, usually after data was loaded with explicit IDs. Check MAX(id) and move the sequence past it (ALTER SEQUENCE ... RESTART START WITH max+1). For identity columns use START WITH LIMIT VALUE.

The constraint name from ORA-00001 is not in USER_CONSTRAINTS. Why?

It is probably a unique index created with CREATE UNIQUE INDEX, not a constraint. Look in USER_INDEXES; for a function-based index the expression is in USER_IND_EXPRESSIONS.

How do I insert a row or update it if it already exists?

Use MERGE INTO ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT. It updates the existing row instead of raising ORA-00001.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors