ORA-00001
There're only two types of DML statement, INSERT and UPDATE, may throw the error, ORA-00001: unique constraint violated. ORA-00001 means that there's a constraint preventing you to have the duplicate value combination. Most likely, it's an unique constraint. That's why your INSERT or UPDATE statement failed to work.
Let's see some cases.
1. INSERT
We inserted into a row that violate the primary key.
SQL> insert into employees (employee_id, last_name, email, hire_date, job_id) values (100, 'Rowe', 'SCOTTI', to_date('17-JAN-22', 'DD-MON-RR'), 'AC_MGR');
insert into employees (employee_id, last_name, email, hire_date, job_id) values (100, 'Rowe', 'SCOTTI', to_date('17-JAN-22', 'DD-MON-RR'), 'AC_MGR')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated
In the error message, it told us that we specifically violate HR.EMP_EMP_ID_PK.
Please note that, not all primary keys are unique, it's allowable to have non-unique primary keys.
2. UPDATE
We updated a row that violate an unique index.
SQL> update employees set email = 'JROWE' where employee_id = 100;
update employees set email = 'JROWE' where employee_id = 100
*
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated
In the error message, it told us that we specifically violate HR.EMP_EMAIL_UK.
Solution
To solve ORA-00001, we should use a different value to perform INSERT INTO or UPDATE SET statement. The solution may sound easy to say, but hard to do, because we may not know what columns we violated.
Check Constraint Columns
So let's see how we check unique columns.
SQL> column table_name format a20;
SQL> column column_name format a20;
SQL> select table_name, column_name, position from all_cons_columns where owner = 'HR' and constraint_name = 'EMP_EMAIL_UK';
TABLE_NAME COLUMN_NAME POSITION
-------------------- -------------------- ----------
EMPLOYEES EMAIL 1
The above query tells us that the column combination in the output is violated. To comply with the unique constraint, you can almost do nothing except for checking the existing row.
Drop Unique Constraint to Prevent ORA-00001
An alternative solution is to drop the unique index if it's not necessary anymore. Dropping a primary or unique index needs more skills, otherwise you might see ORA-02429.
In a multithread environment, you may check whether the row is existing or not, then do your INSERT in order to prevent ORA-00001.
declare
v_row_counts number;
begin
select count(*) into v_row_counts from employees where employee_id = 100;
if v_row_counts = 0 then
-- Insert the row
else
-- Do not insert the row
end if;
end;
/
In the above block of code, if the row count is 0, then we can do INSERT right after counting, elsewhere don't do it.
I need your help for OHS online registration. Please give advice
Oracle error never lies to you. The violated unique key was shown in ORA-00001 error message, you have to check the index and then remove the duplicate one before another registration with the same key.
dear sir still I not able to registered in Oshad due to below volition please advice me a essay way how can I registered in Oshad ,
# Note – when we try to registered this below error are came .
RA-00001: unique constraint (ACT_TSI.EHS_APPLICANTS__UN) violated
We have talked about the formal way to solve ORA-00001. If you really don’t care about the duplication, you may consider to drop the constraint then drop the unique index.
I need help to resolve the constraint error and ask to the person how to solve it:
Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (CABISACEV.SYS_C009243) violated
The error may involve com.gemaltocogent.ams.db.oracle.mapper.OracleArchiveMapper.openCase-Inline
### The error occurred while setting parameters
### SQL: insert into t_case(p_case_id,latent_case,status,create_time,comments,f_create_user,case_info,f_note_id) select p_case_id,latent_case,0,create_time,comments,f_create_user,case_info,f_note_id from t_case_archive where p_case_id = ?
### Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (CABISACEV.SYS_C009243) violated
; SQL []; ORA-00001: unique constraint (CABISACEV.SYS_C009243) violated
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (CABISACEV.SYS_C009243) violated
,cause:org.springframework.dao.DuplicateKeyException:
### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (CABISACEV.SYS_C009243) violated
In your case, the unique index CABISACEV.SYS_C009243 was violated. That’s the one you need to drill down.
yes i got after analyze the logs the filed to be update but in another case the constraint came over the applicative and after reset the operation the violation message disappear.
Good for you!