ORA 000001 Unique constraint violated

Question

I have an Insert statement to a table which has a primary key of dep_id and emp_id. My java program generates a new emp_id for a new record and inserts it. What is happening is if for example, I have dep_id = 100 and emp_id = 25, I am not able to insert a record where dep_id = 100 and emp_id = 26, but I can for dep_id = 100 and emp_id = 27. I checked thru select statements if something of that combination (dep_id = 100 and emp_id = 26) exists. There is nothing of that sort. I still did a DELETE from where dep_id = 100 and emp_id = 26 and Commit just to be sure and then tried to insert, but still it does not work. What might be wrong? Follwing is the code: Modified DDL and Insert statement(Obtained from Eclipse console)

                                                                                    CREATE  TABLE "TestDB"."table1" 
 (  "dep_id" NUMBER(20,0), 
"emp_id" NUMBER(20,0), 
"STATUS" VARCHAR2(10 BYTE), 
"PRO_LEVEL" VARCHAR2(14 BYTE), 
"new_sql_stmt" VARCHAR2(4000 BYTE), 
"DEL_TEM" VARCHAR2(500 BYTE), 
"tab_name" VARCHAR2(4000 BYTE), 
"COL_NAME" VARCHAR2(4000 BYTE), 
"QUERY_TYPE" VARCHAR2(4000 BYTE), 
"NAME" VARCHAR2(4000 BYTE), 
"DT_MODIFIED" DATE DEFAULT SYSDATE

)

  CREATE UNIQUE INDEX "TestDB"."table1_PK" ON "TestDB"."table1" ("dep_id", "emp_id") 


     INSERT into table1            (dep_id,emp_id,status,new_sql_stmt,tab_name,col_name,query_type,NAME)values('100','26','Unlock','INSERT into testTab(id_some,nm_some,id_some_origin,flag,some_code,author,order) values  (''S11111111'',''trialSome00'','''',''y'','''',''100'',0)','testTab','nm_some','INSERT','trialSome00')

Please note that the Insert statement itself has another Insert statement as the value. This Insert statement(The main one) is used in many other places in the app. Also, The dep_id that I use 100 is a test dep_id. Nobody uses it but me.

Are you certain that your java code is synchronized? It may be getting called by multiple threads. — Chetter Hummin, Jul 11 '12 at 16:18
What you're describing doesn't make sense. Either there are parts that you are leaving out (i.e. another session, potentially running a different application, is inserting and committing a row with `dep_id=100` and `emp_id=26`) or your description is incorrect. Can you post the DDL to create the table and post some sample code that we can run on our machines to replicate the problem? — Justin Cave, Jul 11 '12 at 16:23
@Justin, I have edited my original question to post some SQL code — Raghu, Jul 11 '12 at 17:06
How are you generating the new id for the record to be inserted? — Alfabravo, Jul 11 '12 at 17:15
@alfabravo A java method executes this SQL "select max(emp_id) from table1 where dep_id = '100'" It adds 1 to the number and returns it. That part is just fine. I have rechecked it multiple times on SQL developer. — Raghu, Jul 11 '12 at 17:20
@Raghu - You know that `max(emp_id) + 1` will not work in a multi-user environment, right? Two sessions running at the same time will see the same `max(emp_id)` and will try to insert rows with identical `emp_id` values leading to the error you describe. Is there a reason that you're not using sequences to generate your keys? — Justin Cave, Jul 11 '12 at 17:29
Nouuu, let's get to the choppa! Ñññaaggghhhhh... multiuser + select max = you don't want to live. Come with me to use of sequences/auto_increment if you want to live :) — Alfabravo, Jul 11 '12 at 17:32
I understand what you say(max not working). But please consider that this dep_id is used only by me and this sort of insert has worked for a long time now. Only from yesterday, this is behaving like this. I used SQL developer and tried to do the above insert. The progress bar just goes on and on and eventually hangs. If I change the emp_id to say 27, the insert works fine. Just baffles me!! — Raghu, Jul 11 '12 at 17:46

Bill Rosmus · Accepted Answer · 2012-07-11T18:31:31.780

Create an Oracle sequence:

CREATE SEQUENCE emp_id_seq MINVALUE 1 START WITH 1 INCREMENT BY 1;

Now you can either call the "nextval" method on that sequence (this can be done with JDBC or almost every ORM if not all of them) from within your code, or you can place a trigger on that column that automatically gets the nextval from the sequence and inserts it in the table any time you do an insert (IIRC on oracle you would use the 'BEFORE INSERT' operator in your trigger).

e.g.

create or replace trigger mytable_emp_id_trigger
before insert on mytable
for each row
begin
    select emp_id_seq.nextval into :emp.id from dual;
end;
/

Doing it this way is thread safe and you should never see a conflict like this occur.

If you insist on doing it within java code (say the DBA's won't let you create another sequence, but why-ever not I'd be surprised if they didn't), then you'll have to make that method thread safe. Likely best to create a singleton (you can create singleton EJBs) with a synchronized method to provide the next value. You get your id's from that singleton class, not any other class or ejb to avoid issues like you are having now (whether or not the current issue is because of this, what you are experiencing now is typical of multiple threads coming into conflict with unsynchronized methods in a multithreaded environment).

In the meantime, make use of your debugger and watch your variables to see what your existing code is producing if you really want to understand what is happening. Debuggers and watches are your friends. Your best friends.

Thanks BillR and everybody who tried to help me today. While I still dont know what is causing the issue, I was also thinking of having to change the way the next value is obtained. I will go with this. — Raghu, Jul 11 '12 at 18:42

ORA 000001 Unique constraint violated

1 Answers1