Re: Forcing uniqueness using triggers, ORA-4091

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 30 Dec 1998 16:17:42 GMT
Message-ID: <368f505b.11349880@192.86.155.100>

A copy of this was sent to Brian Rasmusson <br_at_belle.dk> (if that email address didn't require changing) On Wed, 30 Dec 1998 16:36:05 +0100, you wrote:

>Hi all,
>
>i have a special case where i would like to enforce uniqueness using a
>trigger, because i cannot use a unique constraint.
>
>Consider the following table structure:
>
>number_col1 NOT NULL NUMBER
>number_col2 NOT NULL NUMBER
>number_col3 NUMBER
>varchar_col1 VARCHAR(32)
>
>primary key is number_col1 and number_col2.
>
>Now, varchar_col1 is optional but must be unique for number_col1.
>Normally i would have made a UNIQUE constraint on(number_col1,
>varchar_col1) but this cannot be done because varchar_col1 may be NULL
>for several rows.
>
>Whats the most elegant way to do this check? I know that if i create a
>trigger on the table, i cannot select from the same table because i will
>run into the ever annoying mutating table problem (ORA-4091).
>

Not only that, but you cannot enforce uniqueness in a trigger (even if you processed the data in a AFTER trigger that could read the table). Uniqueness cannot be enforced in a multi-user system via a trigger -- period.

The reason is multi-versioning. Your trigger will see your inserts but no one elses (until they commit). If 2 people did an operation close enough in time to eachother, you would end up with undetected dups. You would have to serialize access to the table if you really really wanted to do this in a trigger.

I'm curious why this doesn't work in your opinion tho? I just ran:

SQL> create table t ( nc1 int, nc2 int, nc3 int, vc1 varchar2(32),

  2                  primary key(nc1,nc2),
  3                  unique(nc1,vc1) )

  4 /

Table created.

SQL>
SQL> insert into t values ( 1,1,1,null );

1 row created.

SQL> insert into t values ( 1,2,1,null ); insert into t values ( 1,2,1,null )

            *
ERROR at line 1:
ORA-00001: unique constraint (TKYTE.SYS_C0032953) violated

And it enforced the uniqueness of nc1, vc1 for me. Is it that you want this to be allowed but if you attempted to insert:

(1,1,1,1)
(1,2,1,1)

that would fail? If so, we can do that by adding another column and using a trigger. Consider:

SQL> create table t ( nc1 int, nc2 int, nc3 int, vc1 varchar2(32), extra int,

  2                  primary key(nc1,nc2),
  3                  unique(nc1,vc1,extra) )

  4 /

Table created.

SQL>
SQL> create or replace trigger t_trigger   2 before insert or update on t
  3 for each row
  4 begin

  5          if ( :new.vc1 is null )  then
  6                  :new.extra := :new.nc2;
  7          else
  8                  :new.extra := NULL;
  9          end if;

 10 end;
 11 /

Trigger created.

SQL>
SQL> insert into t(nc1,nc2,nc3,vc1) values ( 1,1,1,null );

1 row created.

SQL> insert into t(nc1,nc2,nc3,vc1) values ( 1,2,1,null );

1 row created.

SQL>
SQL> insert into t(nc1,nc2,nc3,vc1) values ( 2,1,1,1 );

1 row created.

SQL> insert into t(nc1,nc2,nc3,vc1) values ( 2,2,1,1 ); insert into t(nc1,nc2,nc3,vc1) values ( 2,2,1,1 )

            *
ERROR at line 1:
ORA-00001: unique constraint (TKYTE.SYS_C0032971) violated

That way, the NULLS are uniqued (by using in this case ,nc2 in conjunction with nc1 which we already know is unique) but we still reject unique filled in values of nc1, vc1...

>Any hints will be greatly appreciated.
>
>Happy new year to you all,
>
>Brian
 

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

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Dec 30 1998 - 10:17:42 CST