Home » SQL & PL/SQL » SQL & PL/SQL » ora 00001 unique contraint hr.jhist_emp_id_st_date_pk violated (Oracle 11g)
ora 00001 unique contraint hr.jhist_emp_id_st_date_pk violated [message #570020] Sun, 04 November 2012 14:49 Go to next message
yoley57
Messages: 1
Registered: November 2012
Junior Member

Hello,

Can someone please help me with the error message below. I also put in the relevant code in case it's needed.

SQL> @lab_05_01.sql
SQL> -- Oracle Database 10g: Administration Workshop II
SQL> -- Oracle Server Technologies - Curriculum Development
SQL> --
SQL> -- ***Training purposes only***
SQL> -- ***Not appropriate for production use***
SQL> --
SQL> -- This script performs a batch promotion update.
SQL> -- The logic of the updates is not important -
SQL> -- just that it does updates many rows that
SQL> -- involve more than one table, such that it would
SQL> -- be difficult to figure out how to undo it manually
SQL> -- once committed.
SQL> -- It should be run in by the hr user.
SQL>
SQL> set echo on
SQL>
SQL> update employees set department_id = 90 where job_id = 'IT_PROG';
update employees set department_id = 90 where job_id = 'IT_PROG'
*
[color=red]ERROR at line 1:
ORA-00001: unique constraint (HR.JHIST_EMP_ID_ST_DATE_PK) violated
ORA-06512: at "HR.ADD_JOB_HISTORY", line 10
ORA-06512: at "HR.UPDATE_JOB_HISTORY", line 2
ORA-04088: error during execution of trigger 'HR.UPDATE_JOB_HISTORY'

SQL>
SQL> update employees e set salary = least(e.salary,(select (min_salary + max_sa
lary)/2 * 1.10 from jobs j where j.job_id = e.job_id)) where job_id not like 'AD
_%';

103 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select text from user_source where name = 'UPDATE_JOB_HISTORY';

TEXT
--------------------------------------------------------------------------------

TRIGGER 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;

7 rows selected.

SQL> select text from user_source where name = 'ADD_JOB_HISTORY';

TEXT
--------------------------------------------------------------------------------

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)

TEXT
--------------------------------------------------------------------------------

VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

13 rows selected.

SQL> select column_name from user_cons_columns where
2 constraint_name = 'JHIST_EMP_ID_ST_DATE_PK';

COLUMN_NAME
--------------------------------------------------------------------------------

EMPLOYEE_ID
START_DATE

SQL>
Re: ora 00001 unique contraint hr.jhist_emp_id_st_date_pk violated [message #570021 is a reply to message #570020] Sun, 04 November 2012 15:06 Go to previous messageGo to next message
cookiemonster
Messages: 11074
Registered: September 2008
Location: Rainy Manchester
Senior Member
Seems obvious - start_date is being set to :old.hire_date.
Which'll be the same as it was last time a record was written to job_history.

The code is obviously wrong. What it should be instead I don't know.
Re: ora 00001 unique contraint hr.jhist_emp_id_st_date_pk violated [message #570022 is a reply to message #570020] Sun, 04 November 2012 15:07 Go to previous message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand in the message?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Previous Topic: FORALL INSERT not supported on remote tables
Next Topic: Best solution to retrieve result
Goto Forum:
  


Current Time: Thu Oct 23 13:22:04 CDT 2014

Total time taken to generate the page: 0.08222 seconds