What to do in case of sql insert if not exists?
My question is if there's an atomic insert-if-not-exist statement in Oracle Database? This is not part of ANSI SQL so each database vendor has his own way of doing this, if supported.
To qualify the statement must work under high concurrent load and never throw duplicate key errors. The statement must either succeed (row inserted) or do nothing (PK value already exists).
I've done a bit of Googling and my temporary conclusion is that Oracle does not have support for this, so the only viable route is a try-insert-catch-and-ignore-dup-key-error. Really just wanted help in either verifying or rejecting this assumption.
In case of sql insert if not exists -
If 2 transactions try to MERGE a new value, the second one will get an ORA-00001 after the first one commits.
Session 1:
SQL> create table t1 (id number not null, constraint t1_pk primary key(id));
Table created.
SQL> merge into t1 using (select 1 as id from dual) s on (t1.id = s.id)
when not matched then insert (id) values (s.id);
1 row merged.
SQL>Session 2 (waits for Session 1):
SQL> merge into t1 using (select 1 as id from dual) s on (t1.id = s.id)
when not matched then insert (id) values (s.id);Session 1:
SQL> commit;
Commit complete.
SQL>
Session 2:
merge into t1 using (select 1 as id from dual) s on (t1.id = s.id)
when not matched then insert (id) values (s.id)ERROR at line 1:
ORA-00001: unique constraint (BP.T1_PK) violated
SQL>
However, Oracle provides the IGNORE_ROW_ON_DUPKEY_INDEX hint, which will suppress the ORA-00001 and the second transaction can finish without an error and it will not insert the offending row(s).
Session 1:
SQL> insert /*+ ignore_row_on_dupkey_index(t1,t1_pk) */ into t1 values (2);
1 row created.
SQL>
Session 2 (waiting for Session 1):
SQL> insert /*+ ignore_row_on_dupkey_index(t1,t1_pk) */ into t1 values (2);
Session 1:
SQL> commit;
Commit complete.
SQL>
Session 2:
0 rows created.
SQL>