[FIXED] When I update the same row in a table in SQL more than once, it gives an ORA-00001: unique constraint error

Issue

I am trying to update a row in a table in SQL back to back and am getting an error when running the second update statement.

update employees 
set department_id = 50
where employee_id = 100;

update employees 
set department_id = 60
where employee_id = 100;

I'm getting an error in the second update statement.

ORA-00001: unique constraint (ADMIN.JHIST_EMP_ID_ST_DATE_PK) violated
ORA-06512: at "ADMIN.ADD_JOB_HISTORY", line 10
ORA-06512: at "ADMIN.UPDATE_JOB_HISTORY", line 2
ORA-04088: error during execution of trigger 'ADMIN.UPDATE_JOB_HISTORY'

I was expecting employee_id 100 to be assigned to department_id 60 after the second update.

I am not trying to insert a new record, just trying to update an existing one, so why is there a unique constraint error. Thanks.

Solution

Yes, you aren't doing anything wrong (according to update statements you posted), but it is the UPDATE_JOB_HISTORY database trigger which does something in the background.

Check its code; looks like it calls a procedure (update_job_history) which calls yet another one (add_job_history), and it violated JHIST_EMP_ID_ST_DATE_PK primary key constraint in line #10.

Answered By - Littlefoot

Answer Checked By - Marie Seifert (PHPFixing Admin)