Oracle SQL: Insert duplicate key (disable/enable constraints)

0
votes

I am trying to INSERT some rows in a table, but since some of the rows are duplicate, I get a "unique constraint violated" error for one of the constraints.

Therefore, I decided to disable the constraints, insert the rows, delete the duplicate rows, and finally enable the constraints again. However, even after disabling the constraints, I get the same error message. It looks like the disabling hasn't worked. (I have checked the disable constraints script previously, and am pretty sure that it worked).

2
sqloraclesql-insertunique-constraint
Are their foreign keys that refer to the key column in this table? Because I don't think you can disable constraints when an fk points to the column. - Michael Kohne
The error I get is not for a foreign/primary key: *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. - Matin
Why don't you switch to a MERGE instead? - dnoeth
Can you post the exact error message? You disabled the unique constraint, maybe there is a unique index on that column that is still enabled. - ruudvan
@ruudvan is right. The unique constraint violation will appear due to a unique index on the data. - Beege

2 Answers

0
votes

May I show you one scenario and maybe it's your case:

Let's create table and unique constraint:

create table AG_TW
(a NUMBER,
 b VARCHAR2(5));

alter table AG_TW
  add constraint AG_UK_AG_TW unique (A);

fill this table:

insert into ag_tw
select level, 'V'||level
from dual
connect by level <=10;

disable constraint and insert duplicates again:

alter table AG_TW
  disable constraint AG_UK_AG_TW;

insert into ag_tw
select level, 'V'||level
from dual
connect by level <=10;

As result: 10 rows inserted.

Now let's drop this constraint: 

alter table AG_TW
  drop constraint AG_UK_AG_TW cascade;

Next I create unique index and add constraint based on this index:

create unique index AG_UK_AG_TW on AG_TW (a);

alter table AG_TW
  add constraint AG_UK_AG_TW unique (A) using index AG_UK_AG_TW;

Then disable this constraint and try to insert duplicates:

alter table AG_TW
  disable constraint AG_UK_AG_TW;

insert into ag_tw
select level, 'V'||level
from dual
connect by level <=10;

ORA-00001: unique constraint violated

0
votes

I have a better workaround for this if you are using oracle 11g or above.

You can use below example to simply ignore duplicate values no need to enable disable any constraint. Hope this helps.

insert /*+  ignore_row_on_dupkey_index(unique_cust,index_name) */
into
   unique_cust
(select * from non_unique_cust);