Re: Do no duplicates table exists ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 09 Sep 1999 08:05:34 -0400
Message-ID: <2KDXN3mNON87e3BYKJIog2grzkdp@4ax.com>

A copy of this was sent to krillo_at_my-deja.com (if that email address didn't require changing) On Thu, 09 Sep 1999 07:40:06 GMT, you wrote:

>Hello, I would like to know if oracle 8+ allows
>to create a table wich has automatically no
>duplicates. I mean, wich will ignore any
>insertion of a duplicate row. This sort of thing
>exists in sql server, with some NO_DUP_KEY
>constraint/index. But does it in Oracle 8+ ?
>

No -- not directly. SQL Server as I recall has a mode whereby if I insert a duplicate record (one that would violate a primary key for example) it'll silently ignore this. Oracle does not have such a mode directly.

Starting with Oracle8 (8.0 and up) there is a way to do this. It would look like this:

tkyte_at_8.0> create table t_table ( x int primary key ); Table created.

tkyte_at_8.0> create or replace view t as select * from t_table; View created.

tkyte_at_8.0> create or replace trigger t_trigger   2 INSTEAD OF INSERT ON T
  3 for each row
  4 begin
  5 insert into t_table values ( :new.x );   6 exception

  7          when DUP_VAL_ON_INDEX then
  8                  null;

9 end;
10 /
Trigger created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 2 );

1 row created.

tkyte_at_8.0>
tkyte_at_8.0> select * from t;

         1
         2

You would expose to your developers the view T -- not the table T_TABLE. T will behave like 'NO_DUP_KEY' is on. You could setup a package to allow you to enable/disable this behaviour -- for example:

tkyte_at_8.0> create or replace package globals   2 as
  3 no_dup_key boolean default FALSE;   4 end;
  5 /

Package created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace trigger t_trigger

  2 INSTEAD OF INSERT ON T
  3 for each row
  4 begin
  5 insert into t_table values ( :new.x );   6 exception

  7          when DUP_VAL_ON_INDEX then
  8                  if ( globals.no_dup_key )
  9                  then
 10                          null;
 11                  else
 12                          raise;
 13                  end if;

14 end;
15 /

Trigger created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> insert into t values ( 1 );
insert into t values ( 1 )
            *

ERROR at line 1:

ORA-00001: unique constraint (TKYTE.SYS_C007832) violated
ORA-06512: at "TKYTE.T_TRIGGER", line 9
ORA-04088: error during execution of trigger 'TKYTE.T_TRIGGER'

tkyte_at_8.0>
tkyte_at_8.0> exec globals.no_dup_key := TRUE;

PL/SQL procedure successfully completed.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0>
tkyte_at_8.0> select * from t;

         1
         2

so, using the package you must enable the NO_DUP_KEY behaviour explicitly by setting the package variable (affects your SESSION only -- other sessions have their own package states)

>Thanks
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 09 1999 - 07:05:34 CDT