Xref: alice comp.databases.oracle.misc:40635 Path: alice!news-feed.fnsi.net!hammer.uoregon.edu!arclight.uoregon.edu!logbridge.uoregon.edu!newsfeed.stanford.edu!paloalto-snf1.gtei.net!news.gtei.net!inet16.us.oracle.com!not-for-mail From: Thomas Kyte Newsgroups: comp.databases.oracle.misc Subject: Re: Do no duplicates table exists ? Date: Thu, 09 Sep 1999 08:05:34 -0400 Organization: Oracle Service Industries Lines: 160 Message-ID: <2KDXN3mNON87e3BYKJIog2grzkdp@4ax.com> References: <7r7o8k$trt$1@nnrp1.deja.com> Reply-To: tkyte@us.oracle.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: inet16.us.oracle.com 936878684 18603 138.1.114.204 (9 Sep 1999 12:04:44 GMT) X-Complaints-To: usenet@inet16.us.oracle.com NNTP-Posting-Date: 9 Sep 1999 12:04:44 GMT X-Newsreader: Forte Agent 1.6/32.525 A copy of this was sent to krillo@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@8.0> create table t_table ( x int primary key ); Table created. tkyte@8.0> create or replace view t as select * from t_table; View created. tkyte@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@8.0> insert into t values ( 1 ); 1 row created. tkyte@8.0> insert into t values ( 1 ); 1 row created. tkyte@8.0> insert into t values ( 1 ); 1 row created. tkyte@8.0> insert into t values ( 1 ); 1 row created. tkyte@8.0> insert into t values ( 1 ); 1 row created. tkyte@8.0> insert into t values ( 2 ); 1 row created. tkyte@8.0> tkyte@8.0> select * from t; X ---------- 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@8.0> create or replace package globals 2 as 3 no_dup_key boolean default FALSE; 4 end; 5 / Package created. tkyte@8.0> tkyte@8.0> tkyte@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@8.0> tkyte@8.0> tkyte@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@8.0> tkyte@8.0> exec globals.no_dup_key := TRUE; PL/SQL procedure successfully completed. tkyte@8.0> insert into t values ( 1 ); 1 row created. tkyte@8.0> insert into t values ( 1 ); 1 row created. tkyte@8.0> insert into t values ( 1 ); 1 row created. tkyte@8.0> tkyte@8.0> select * from t; X ---------- 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@us.oracle.com Oracle Service Industries Reston, VA USA Opinions are mine and do not necessarily reflect those of Oracle Corporation