Skip to Main Content
  • Questions
  • Modify Insert statement to avoid Primary Key constraint.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jazz.

Asked: January 21, 2016 - 10:51 pm UTC

Answered by: Connor McDonald - Last updated: January 31, 2016 - 4:15 am UTC

Category: Database - Version: Oracle 11g R2

Viewed 10K+ times! This question is

You Asked

I am dealing with a 3rd party legacy application, in which the code cannot be changed.

Periodically, this application inserts rows in a table which causes it to have a Primary key error.

I need a way to "stop Oracle" from raising a exception of "ORA-00001: unique constraint" when bad rows get inserted.

At first I tried to use a "before insert" trigger, but this got me with messages about mutating table. Ugh.

I did some research and found a couple of techniques would could help me, but they both required a change
to the insert statement.

Those techniques are using:

dbms_errlog.create_error_log

This require that the insert statement is modified to include the following statement. Assume I have a table called T

log errors into err$_t ('trap_pk')
reject limit unlimited

Another approach I thought of is to use the "hint" of /*+ ignore_row_on_dupkey_index(t, t_pk) */
Again, I would need to modify the statement!

So, how can I achieve this?

Stored outlines? The statement that fire doesn't use bind variables, so not sure If I can go this way.
Sql Profiles?
Sql Plan Management?

I am not sure 100% If I can do what I need to do...

Here is my example to play with....
create table t ( x int );
alter table t add constraint t_pk primary key(x);

insert into t values ( 1);
insert into t values ( 1);      << This would give me the primary key exception
insert  /*+ ignore_row_on_dupkey_index(t, t_pk) */  into t values ( 1);  << THIS DOES NOT give me error, just silently "ignores" it! I want this
to happen dynamically without having to change the code.

So, how can I "inject" this "hint" into the query? What approach and can you share any code?

Thanks again for any advice.


Thanks.

and we said...

I think you might be out of luck here. All of the facilities for injecting hints are typically for *reading* data (even if those reads are part of an update etc). To quote the docs from (for example) outlines:

"You can specify any one of the following statements: SELECT, DELETE, UPDATE, INSERT-SELECT, CREATE TABLE AS SELECT."

Similarly, looking at DBMS_ADVANCED_REWRITE, it doesnt appear to support binds.

There is DBMS_SQL_TRANSLATOR, but that's a 12c feature.

I must admit, this strikes me as not the best way to tackle the problem, because of the risks (ie, you'd never know if you'd rejected 1 rows or 10 million rows over the lifetime of the table).

The only thing I could think of would be replacing all the tables with views of the same name, and using instead-of triggers, but that seems a drastic approach.

and you rated our response

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

deferrable pk + after stmt trigger

January 22, 2016 - 10:00 am UTC

Reviewer: Markus from Switzerland

SQL> create table t1 (col1 number primary key deferrable initially deferred);

Table T1 erstellt.

SQL> create trigger trg1
2 after insert on t1
3 begin
4 DELETE FROM
5 t1 A
6 WHERE
7 a.rowid >
8 ANY (
9 SELECT
10 B.rowid
11 FROM
12 t1 B
13 WHERE
14 A.col1 = B.col1
15 );
16 end;
17 /

Trigger TRG1 kompiliert

SQL> insert into t1 values (1);

1 Zeile eingefügt.

SQL> insert into t1 values (1);

1 Zeile eingefügt.

SQL> commit;

Commit abgeschlossen.
SQL> select * from t1;

COL1
----------
1

1 row selected
Connor McDonald

Followup  

January 22, 2016 - 11:48 am UTC

Things go a little bit nasty with that at scale:

SQL> create table t1
  2  as select rownum x, rpad(rownum,100,'x') y
  3  from
  4    ( select 1 from dual connect by level <= 1000),
  5    ( select 1 from dual connect by level <= 10000);

Table created.

SQL> alter table t1 add primary key (x ) deferrable initially deferred;

Table altered.

SQL> create trigger trg1
  2   after insert on t1
  3   begin
  4   DELETE FROM
  5   t1 A
  6   WHERE
  7   a.rowid >
  8   ANY (
  9   SELECT
 10   B.rowid
 11   FROM
 12   t1 B
 13   WHERE
 14   A.x = B.x
 15   );
 16   end;
 17   /

Trigger created.

SQL> set timing on
SQL>
SQL> insert into t1 values (10000,'blah');

1 row created.

Elapsed: 00:00:21.49

SQL> insert into t1 values (10000,'blah');

1 row created.

Elapsed: 00:00:17.80

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL>


But this is not in any way to dismiss your solution - I think its a very nifty mechanism. I'd be inclined to tweak the trigger a little to sort out the performance:

SQL> create or replace
  2  trigger trg1
  3  for insert
  4  on T1 compound trigger
  5
  6  --
  7  -- an array structure to buffer all the row changes
  8  --
  9    type t_pk_list is
 10      table of number index by pls_integer;
 11
 12    l_pk_rows      t_pk_list;
 13
 14  before statement is
 15  begin
 16    l_pk_rows.delete;
 17  end before statement;
 18
 19  after each row is
 20  begin
 21    l_pk_rows(l_pk_rows.count+1)   := :new.x;
 22  end after each row;
 23
 24  after statement is
 25  begin
 26    forall i in 1 .. l_pk_rows.count
 27       delete from t1
 28       where rowid in
 29       ( select rid
 30         from
 31         ( select rowid rid, row_number() over ( order by rowid) as r
 32           from   t1
 33           where  x = l_pk_rows(i)
 34         )
 35         where r > 1
 36       );
 37
 38    l_pk_rows.delete;
 39  end after statement;
 40
 41  end;
 42  /

Trigger created.

Elapsed: 00:00:00.03
SQL>
SQL> set timing on
SQL>
SQL> insert into t1 values (10000,'blah');

1 row created.

Elapsed: 00:00:00.02
SQL>
SQL> insert into t1 values (10000,'blah');

1 row created.

Elapsed: 00:00:00.00


but your idea is cool.

After each row section of compound trigger

January 22, 2016 - 1:58 pm UTC

Reviewer: Rajeshwaran, Jeyabal

If it coded this way, that could benefit multirow/bulk inserts too.

19  after each row is
20  begin
21    l_pk_rows(:new.x) := :new.x;
22  end after each row;

Connor McDonald

Followup  

January 23, 2016 - 2:51 am UTC

Sorry - you've lost me here. Can you elucidate.

January 22, 2016 - 5:13 pm UTC

Reviewer: Jazz from Ontario

Thanks, I kind of figured so.
Connor McDonald

Followup  

January 23, 2016 - 3:01 am UTC

Note- with the compound trigger I posted - this might do the trick for you with reasonable performance.

January 22, 2016 - 5:49 pm UTC

Reviewer: Jazz from Ontario

Thanks Connor and Markus

Very creative approach and code!

This table would have great than 100 million rows and constantly grows, so, I don't think it would scale well.

But, since it's just happening in 1 table, I will investigate the instead of trigger approach.



Connor McDonald

Followup  

January 23, 2016 - 3:02 am UTC

See my previous followup. The compound trigger approach might be suitable.

January 26, 2016 - 11:02 pm UTC

Reviewer: Jazz from TO

Hello again,

I tested the code you provided, indeed it worked! Your T1 table generated had 10 million rows and it held up well!!!
The code that insert uses an anyonymous transaction to pound in hundred of rows at a time..and see any performance woes..(yet)
I am beginning to think this can scale up to my 100 million rows + I mentioned... :)

Trying to understand the "magic" in the solution, ...the key elements are the deferrable constraint to allow the trigger to fire
and do it's stuff without raising pk error, as well as the internal buffer in the trigger
to control and have ability to deal with multiple rows coming in.

Now, of course in my example, I created the table t1 just as a simple table just to demonstrate my problem.

The real table and pk is quite different.

I am having problems trying grasp and re-code the trigger to accommodate.

My table is really more like T3 shown below. And the primary is comprised of mulitple columns.

DROP TABLE T3;

CREATE TABLE T3
(
TID NUMBER(10,0),
LAT NUMBER(10,0),
LON NUMBER(10,0),
MDATE NUMBER(10,0),
MTIME NUMBER(10,0)
);

ALTER TABLE T3 ADD CONSTRAINT PK_T3 PRIMARY KEY (MDATE, TID, MTIME, LAT, LON) deferrable initially deferred;

INSERT INTO T3 (TID, LAT, LON, MDATE, MTIME) VALUES (3197063, 44736760, -92852630, 20160121, 32496);
INSERT INTO T3 (TID, LAT, LON, MDATE, MTIME) VALUES (3197063, 44736760, -92852630, 20160121, 32496);

Can you help me understand how to re-create the trigger to match T3?

Thanks again for all your and others help in this solution.
Connor McDonald

Followup  

January 27, 2016 - 2:10 am UTC

OK, the original trigger has "pk_list" as a table of number, because in my demo "number" is the primary key.

In your case, the primary key is: (a,b,c,d) so you want a table of that, hence (pseudocode below)

type pk is record
( a number, b number, c number, d number)

type pk_list is table of pk index by pls_integer;

then at row level we capture the whole primary key

l_pk(l_pk.count+1).a := :new.a;
l_pk(l_pk.count).b := :new.b;
l_pk(l_pk.count).c := :new.c;
l_pk(l_pk.count).d := :new.d;

and then the delete does similar

select rowid rid, row_number() over ( order by rowid) as r
from t1
where a = l_pk_rows(i).a
and b = l_pk_rows(i).b
and c = l_pk_rows(i).c
and d = l_pk_rows(i).d

It worked!

January 30, 2016 - 8:01 pm UTC

Reviewer: Jazz from Ontario

Thank you, I rewrote trigger and it works! :)
Connor McDonald

Followup  

January 31, 2016 - 4:15 am UTC

Glad we could help