Inserting Duplicate Values

This morning I was inserting rows in a table with unique indexes, where it found a duplicate key (natural or composite) it abort the execution. I needed insert all rows excluding the rows, of course, maybe drop the index or filter the rows to insert can be a solution, but in my case the hint IGNORE_ROW_ON_DUPKEY_INDEX was the solution. It only work for insert statement. This hint cause the statement ignore a unique key violation and does not cause statement termination. Please let me show a example.

 
Create a new table with 3 rows;

dbp12> create table anr1 (col1 number);

Tabla creada.

dbp12> insert into anr1 values (1);

1 fila creada.

dbp12> insert into anr1 values (5);

1 fila creada.

dbp12> insert into anr1 values (6);

1 fila creada.

dbp12> select * from anr1;

      COL1
__________
         1
         5
         6

Create a second table with 3 rows, the first row have value 1.

dbp12> create table anr2 (col1 number);

Tabla creada.

dbp12> insert into anr2 values (1);

1 fila creada.

dbp12> insert into anr2 values (2);

1 fila creada.

dbp12> insert into anr2 values (3);

1 fila creada.

Show values for both tables, and perform commit;

dbp12> select * from anr1;

      COL1
__________
         1
         5
         6

Transcurrido: 00:00:00.29
dbp12> select * from anr2;

      COL1
__________
         1
         2
         3

Transcurrido: 00:00:00.51
dbp12> commit;

Confirmación terminada.

Create a unique index on the first table;

dbp12> create unique index anr1inx on anr1 (col1);

Índice creado.

Now perform a insert/select command, the first value must fail and abort the execution;

dbp12> insert into anr1  select * from anr2;
insert into anr1  select * from anr2
*
ERROR en línea 1:
ORA-00001: restricción única (ANTONION.ANR1INX) violada

dbp12> select * from anr1;

      COL1
__________
         1
         5
         6

Transcurrido: 00:00:00.28

One more timen, now with the ignore_row_on_dupkey_index hint;

dbp12> insert /*+ ignore_row_on_dupkey_index (anr1,ANR1INX) */ into anr1  select * from anr2;

2 filas creadas.

dbp12>  select * from anr1;

      COL1
__________
         1
         5
         6
         2
         3

In this case the values 2 and 3 have been inserted.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s