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
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
CREATE TABLE products (product_id NUMBER CONSTRAINT products_pk PRIMARY KEY, name VARCHAR2(50), price NUMBER);
INSERT INTO products VALUES (100, 'Keyboard', 30);
COMMIT;Table created.
1 row created.
Commit complete.INSERT INTO products VALUES (100, 'Keyboard', 35);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/SELECT * FROM products WHERE product_id = 100;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.
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;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
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;Table created.
5 rows created.
Commit complete.
Sequence created.INSERT INTO orders VALUES (orders_seq.NEXTVAL, 700);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/SELECT MAX(order_id) AS max_id FROM orders; MAX_ID
----------
5
1 row selected.ALTER SEQUENCE orders_seq RESTART START WITH 6;Sequence altered.Use the value MAX + 1 from your own data. Here the maximum was 5, so the sequence restarts at 6.
INSERT INTO orders VALUES (orders_seq.NEXTVAL, 700);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
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;Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.INSERT INTO tickets (title) VALUES ('new ticket');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/ALTER TABLE tickets MODIFY ticket_id GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE);Table altered.INSERT INTO tickets (title) VALUES ('new ticket');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
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;Table created.
Index created.
1 row created.
Commit complete.INSERT INTO users VALUES (2, 'Kim@Example.com');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/SELECT constraint_name FROM user_constraints WHERE constraint_name = 'USERS_EMAIL_UX';no rows selectedSELECT 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';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.
SELECT user_id, email FROM users WHERE LOWER(email) = LOWER('Kim@Example.com'); 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