Updating one record multiple times

Question

I'm encountering an error when updating one record multiple times.

SQL Code:

UPDATE EMPLOYEES 
SET FIRST_NAME='J', 
    LAST_NAME='E', 
    EMAIL='[email protected]',
    PHONE_NUMBER='123456789',
    HIRE_DATE='10/13/2015', 
    JOB_ID=(SELECT JOB_ID FROM JOBS WHERE JOB_TITLE='Programmer'), 
    SALARY=4000, 
    COMMISSION_PCT=0, 
    DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME='IT'),  
    MANAGER_ID=(SELECT MANAGER_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME='IT') 
WHERE EMPLOYEE_ID=15;

The error message:

Error starting at line : 1 in command -
UPDATE EMPLOYEES SET FIRST_NAME='J', LAST_NAME='E', EMAIL='[email protected]', PHONE_NUMBER='123456789',
HIRE_DATE='10/13/2015', 
JOB_ID=(SELECT JOB_ID FROM JOBS WHERE JOB_TITLE='Programmer'), SALARY=4000, COMMISSION_PCT=0, 
DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME='IT'),
MANAGER_ID=(SELECT MANAGER_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME='IT') WHERE EMPLOYEE_ID=15
Error report -
SQL Error: ORA-00001: unique constraint (GAS.JHIST_EMP_ID_ST_DATE_PK) violated
ORA-06512: at "GAS.ADD_JOB_HISTORY", line 10
ORA-06512: at "GAS.UPDATE_JOB_HISTORY", line 2
ORA-04088: error during execution of trigger 'GAS.UPDATE_JOB_HISTORY'
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Here's the trigger:(again from the comment section)

CREATE OR REPLACE TRIGGER "GAS"."UPDATE_JOB_HISTORY" 
AFTER UPDATE OF job_id, department_id 
ON employees FOR EACH ROW 

BEGIN 

add_job_history(:old.employee_id, 
                :old.hire_date, 
                 sysdate, 
                :old.job_id, 
                :old.department_id); 
END; 
 / 

ALTER TRIGGER "GAS"."UPDATE_JOB_HISTORY" ENABLE;

Here's the Code for add_job_history.

create or replace PROCEDURE add_job_history ( p_emp_id job_history.employee_id%type , 
                                              p_start_date job_history.start_date%type , 
                                              p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , 
                                              p_department_id job_history.department_id%type ) 
IS 
BEGIN 

INSERT INTO job_history (employee_id,
                         start_date, 
                         end_date, 
                         job_id, 
                         department_id) 
                  VALUES(p_emp_id, 
                         p_start_date, 
                         p_end_date, 
                         p_job_id, 
                         p_department_id); 
END add_job_history;

Answer 1

The error message tells you exactly why the update is failing:

SQL Error: ORA-00001: unique constraint (GAS.JHIST_EMP_ID_ST_DATE_PK) violated

Assuming your naming convention is trustworthy, it appears you have a primary key on JOB_HISTORY of (EMP_ID, START_DATE). Note that the trigger is passing EMPLOYEE.HIRE_DATE as START_DATE, which means it will be the same for all records in JOB_HISTORY.

Consequently you can have only one record for each employee in that table. This seems rather too restrictive for a journalling table and is probably not what you intended.

So the correct solution would be a way to get the populate JOB_HISTORY with START_DATE values which represent the start of each assignment, and hence would be different for each record.

Alternatively you need to add another column to that key. You could use END_DATE, as it signals the end of old assignment. Or a new column, such as a sequence id or timestamp, which you can use to track the employee's history.

---

You say in a comment that you can't change the data structures at all. That leaves correctly setting the assignment START_DATE. Given that the Job History represents a journal of closed assignments, you can find the start date of the current (about to be closed) assignment by:

select max(end_date) 
from job_history
where employee_id = 15;

The proper place for this logic would be in add_job_history(), and ignore or remove the p_start_date parameter. You could put it in the trigger code, but that's clunkier.