Re: Question on Check Constraints

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 09 Mar 1999 14:47:38 GMT
Message-ID: <36ec305c.91419774@192.86.155.100>

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 Received on Tue Mar 09 1999 - 08:47:38 CST