Re: Question on Check Constraints

Thanks for the insight, Thomas. The situation you describe (possibly common) had not occurred to me.

Cary

In article <36ec305c.91419774_at_192.86.155.100>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to cary_at_nams.net
> (if that email address didn't require changing)
> On Mon, 08 Mar 1999 22:20:16 GMT, you wrote:
>
> >Hi, Kevin,
> >
> > Table check constraints will not allow you to prevent the same key value
> >from appearing in 2 different tables since you cannot use SQL subqueries in
> >check constraints. What you could do is set up 2 database before insert
> >triggers, each of which makes sure that the key value for the current record
> >does not appear in the 'other' table. If it does, the insertion can be made
> >to fail.
> >
>
> that actually won't work as simple as that.
>
> The reason is non blocking reads.
>
> If you have 2 tables t1, t2 and triggers such as:
>
> create trigger t1_trigger
> before insert on t1
> for each row
> declare
> l_cnt number;
> begin
> select count(*) into l_cnt from t2 where id = :new.id;
> if ( l_cnt > 0 ) then
> raise DUP_VAL_ON_INDEX;
> end if;
> end;
> /
>
> (and a similar trigger on t2), then if 2 sessions start and both insert the
> value 1 into each of t1 and t2 -- neither will see eachothers insert until
they
> commit (meaning, they never see eachothers insert). The database will have
> 'dups' across these tables.
>
> The ways to achieve this sort of rule might be:
>
> o use a sequence number as the primary key. have the trigger populate the
> primary key on insert for you. Use the same sequence for both tables. No
need
> to check for dups that way. (easiest approach)
>
> o serialize access to both tables. Don't let anyone insert into t2 when you
> insert into t1 and vice-versa. Use the lock table command to do this.
(ugliest
> approach)
>
> o use dbms_lock to customize the locking in the database to your needs. for
> example (most elegant but perhaps over engineered):
>
> SQL> create table t1 ( x int primary key );
> Table created.
>
> SQL> create table t2 ( x int primary key );
> Table created.
>
> SQL> create or replace trigger t1_bifer
> 2 before insert on t1
> 3 for each row
> 4 declare
> 5 l_lock_id number;
> 6 l_cnt number;
> 7 resource_busy exception;
> 8 pragma exception_init( resource_busy, -54 );
> 9 begin
> 10 l_lock_id := dbms_utility.get_hash_value( to_char( :new.x ), 0, 1024
);
> 11
> 11 if ( dbms_lock.request( id => l_lock_id,
> 12 lockmode => dbms_lock.x_mode,
> 13 timeout => 0,
> 14 release_on_commit => TRUE ) = 1 )
> 15 then
> 16 raise resource_busy;
> 17 end if;
> 18
> 18 select count(*) into l_cnt from t2 where x = :new.x;
> 19 if ( l_cnt > 0 )
> 20 then
> 21 raise DUP_VAL_ON_INDEX;
> 22 end if;
> 23 end;
> 24 /
> Trigger created.
>
> SQL> create or replace trigger t2_bifer
> 2 before insert on t2
> 3 for each row
> 4 declare
> 5 l_lock_id number;
> 6 l_cnt number;
> 7 resource_busy exception;
> 8 pragma exception_init( resource_busy, -54 );
> 9 begin
> 10 l_lock_id := dbms_utility.get_hash_value( to_char( :new.x ), 0, 1024
);
> 11
> 11 if ( dbms_lock.request( id => l_lock_id,
> 12 lockmode => dbms_lock.x_mode,
> 13 timeout => 0,
> 14 release_on_commit => TRUE ) = 1 )
> 15 then
> 16 raise resource_busy;
> 17 end if;
> 18
> 18 select count(*) into l_cnt from t1 where x = :new.x;
> 19 if ( l_cnt > 0 )
> 20 then
> 21 raise DUP_VAL_ON_INDEX;
> 22 end if;
> 23 end;
> 24 /
> Trigger created.
>
> so, now if you fire off 2 sessions and one executes:
>
> insert into t1 values ( 1 );
>
> and the other
>
> insert into t2 values ( 1 );
>
> the second one will FAIL on the dbms_lock request and rollback. If the first
> session commits, and the 2cnd session retries, the 2cnd session will get a
> DUP_VAL_ON_INDEX since it can see the row in t1 now.
>
> This method serializes on the VALUE of the primary key...
>
> >
> >In article <iqVE2.62$gF3.151_at_news15.ispnews.com>,
> > "Kevin Donovan" <kdonovan_at_brainiac.com> wrote:
> >> I have a question regarding the construction of check constraints in Oracle
> >> 8.X. Here is the situation: I have 2 tables in an instance, both have the
> >> same name but different owners. I have a business rule that states that
the
> >> same key cannot exist in both these tables. Is it possible with a check
> >> constraint to enforce this. If it is, any tips you have on creating this
> >> would be very helpful.
> >>
> >> Thanks
> >>
> >> Kevin
> >>
> >>
> >
> >-----------== Posted via Deja News, The Discussion Network ==----------
> >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
> 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
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Mar 09 1999 - 11:33:45 CST