Re: How to define a integrity constraint like this ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/28
Message-ID: <351d20f5.1779528@192.86.155.100>#1/1

A copy of this was sent to sj_at_china.pages.com.cn (if that email address didn't require changing) On Sat, 28 Mar 1998 01:53:57 -0600, you wrote:

>Hello :
>
> I am design a table in oracle 7.3.3 , I want to define a table like this
>
> it has 3 parts:
>
> regno varchar(16)
> usrid varchar(8)
> operate_type number(2)
> finish_flag number(1)
>
> Now , I want to contraint the table to have a constraint like this:
>
> Item in the table , which contains : a special usrid , a special
>operate_type and finish_flag=0 , should be UNIQUE .
>
> the table's pramary key us a regno , which is generate from a sequence .
>

As i understand your requirements you want:

if ( USRID is the 'special userid'

     AND operate_type is the 'special type' 
     AND finish_flag = 0 ) 

then

   the usrid, operate_type must be unique else

   it doesn't have to be unique
end

If so, I would add another column to the table and a sequence in the database. Call the extra column "EXTRA" and make it a generic NUMBER column. Create a trigger such as:

create trigger t
before insert or update of MY_TABLE
for each row
begin

   if ( :new.USRID in ( 'the set of special userid names' )

        AND
        :new.operate_type in ( 'the set of special operate types' )
        AND 
        :new.finish_flag = 0 ) 
   then
        :new.EXTRA = 0;

   elsif ( INSERTING )
   then

        select my_extra_seq.nextval into :new.EXTRA from dual;    end if;
end;
/

CREATE UNIQUE INDEX UINDEX ON MY_TABLE(usrid, operate_type, extra );

this will make sure that the USRID/OPERATE_TYPE columns are unique when finish_flag is zero and the usrid/operate_type value match some critera -- else the extra column will allow them to be non-unique when finish_flag is non-zero or the usrid/operate_type fields are not the special values.

> Thank you very much!
>
> Jing Shen
>
> Mail to: sj_at_china.pages.com.cn
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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 Sat Mar 28 1998 - 00:00:00 CST