Re: Forcing uniqueness using triggers, ORA-4091

Hi,

> >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.
>
> 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.

Makes sence.

> 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:

Exactly.

> 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...

Thanks for the solution. I came up with something similar, but hoped there would be a 'cleaner' way of doing it, that is avoiding that extra column. I'll go for this though, as it seems like it is necessary.

Thanks again!

Brian Received on Mon Jan 04 1999 - 02:06:06 CST