"ORA-00001: unique constraint (constraint_name) violated" Error even though I have NOT EXISTS check

Question

I am trying to insert some values in the table through the application and get the issue "ORA-00001: unique constraint (constraint_name) violated". I have the below table:

CREATE TABLE EMPLOYEE
(
EMP_ID VARCHAR2(32) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
TIME TIMESTAMP(3),
PRIMARY KEY(EMP_ID)
);

I have below statement in procedure and it is failing at this INSERT statement even though I have NOT EXISTS check before inserting:

INSERT INTO EMPLOYEE
(EMP_ID, NAME, TIME)
( SELECT v_empid ,
sys_context('USERENV','SID'),
SYSTIMESTAMP+INTERVAL '10' SECOND 
FROM DUAL
WHERE NOT EXISTS ( SELECT 1
FROM EMPLOYEE
WHERE EMP_ID = v_empid) );

The above procedure is getting invoked from multiple services almost at the same time, Is there something issue with the parallel transactions like multiple sessions are trying to insert into the same table? Any help is appreciated in this, thanks in advance.

Is it always getting that error, or only (sometimes) when run simultaneously? If you always get it - at least for a specific ID - Is the constraint being reported actually your PK, or do you maybe have a trigger on the table that is inserting somewhere else, and that is erroring? — Alex Poole
Its not always. Only when multiple sessions are trying to run the procedure and I have COMMIT just after INSERT statement. It is failing exactly at the same statement as we have only one insert statement in the procedure and the constraint that we are getting is also related to primary key of EMP_ID. — Naveen Reddy CH

ekochergin ekochergin · Accepted Answer · 2020-09-16T08:45:50

To me it looks like 2 sessions are trying to insert same user at the same time.

Here how I reproduced it:

Step 1. Session A:

INSERT INTO test_EMPLOYEE(EMP_ID, NAME, TIME)
(SELECT 123, sys_context('USERENV','SID'), SYSTIMESTAMP+INTERVAL '10' SECOND 
   FROM DUAL
  WHERE NOT EXISTS ( SELECT 1 FROM test_EMPLOYEE WHERE EMP_ID = 123));

Step 2. Session B:

-- same query as Session A
INSERT INTO test_EMPLOYEE(EMP_ID, NAME, TIME)
(SELECT 123, sys_context('USERENV','SID'), SYSTIMESTAMP+INTERVAL '10' SECOND 
   FROM DUAL
  WHERE NOT EXISTS ( SELECT 1 FROM test_EMPLOYEE WHERE EMP_ID = 123));

Step 3. Session A:

commit;

Voila: session B fails with constraint violation

So I believe it's users or something outside of database causing the error

"ORA-00001: unique constraint (constraint_name) violated" Error even though I have NOT EXISTS check

1 Answers