Ora 00001 нарушено ограничение уникальности как исправить

Error message looks like this

Error message => ORA-00001: unique constraint (schema.unique_constraint_name) violated

ORA-00001 occurs when: «a query tries to insert a «duplicate» row in a table». It makes an unique constraint to fail, consequently query fails and row is NOT added to the table.»

Solution:

Find all columns used in unique_constraint, for instance column a, column b, column c, column d collectively creates unique_constraint and then find the record from source data which is duplicate, using following queries:

-- to find <<owner of the table>> and <<name of the table>> for unique_constraint

select *
from DBA_CONSTRAINTS
where CONSTRAINT_NAME = '<unique_constraint_name>';

Then use Justin Cave’s query (pasted below) to find all columns used in unique_constraint:

  SELECT column_name, position
  FROM all_cons_columns
  WHERE constraint_name = <<name of constraint from the error message>>
   AND owner           = <<owner of the table>>
   AND table_name      = <<name of the table>>

    -- to find duplicates

    select column a, column b, column c, column d
    from table
    group by column a, column b, column c, column d
    having count (<any one column used in constraint > ) > 1;

you can either delete that duplicate record from your source data (which was a select query in my particular case, as I experienced it with «Insert into select») or modify to make it unique or change the constraint.

A unique constraint enforces, well, uniqueness. It will allow nulls, unlike a primary key constraint.

Your error means that you are inserting duplicate data when the database has been configured to explicitly prohibit that.

You can find out what constraints are on a table by running the following query on all_constraints. The link decodes the column CONSTRAINT_TYPE, for instance P is a primary key and U a unique key.

select *
  from all_constraints uc
 where uc.table_name = 'MY_TABLE'
   and owner = 'DBSCHEMA'

To find out what columns are in a constraint use all_cons_columns instead, or combining the two into one query:

select uc.*, ucc.column_name, ucc.position
  from all_constraints uc
  join all_cons_columns ucc
    on uc.owner = ucc.owner
   and uc.table_name = ucc.table_name
   and uc.constraint_name = ucc.constraint_name
 where uc.table_name = 'MY_TABLE'
   and uc.owner = 'DBSCHEMA'

To either query you can add the additional condition and constraint_name = 'IDX_CO_DETAILS' to find out details of the specific constraint that seems to be causing your problem.

Your comment is a little surprising for a couple of reasons. Even a system created constraint, for instance one that was defined in-line when the table was created without a name being specified should show up. Also, the constraint name IDX... implies that it’s an index.

IF you run the following query it should tell you if the object exists in the database:

select *
  from all_objects
 where object_name = 'IDX_CO_DETAILS'

I would expect that the OBJECT_TYPE returned by this query is 'INDEX'.

Following on from that the following query will return every index with that name, the type of index, the table it is associated with and the owner of that table.

select *
  from all_indexes
 where index_name = 'IDX_CO_DETAILS'

Judging by your error I would further expect that the column UNIQUNESS returned by this query is 'UNIQUE'.

This should help you track down the object.

You can also use the system package dbms_metadata to track down the DDL of the object; be careful it returns a clob.

select dbms_metadata.get_ddl('INDEX','IDX_CO_DETAILS', schema => 'DBSCHEMA') 
  from dual

the parameter schema is optional.

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, 'Chen', 'EDCHEN', to_date('17-JAN-22', 'DD-MON-RR'), 'AC_MGR');
insert into employees (employee_id, last_name, email, hire_date, job_id) values (100, 'Chen', 'EDCHEN', 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 = 'JCHEN' where employee_id = 100;
update employees set email = 'JCHEN' 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';

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.