Re: SQL question: How to prevent mutually exclusive values?

From: <mpir_at_compuserve.com>
Date: 1998/12/05
Message-ID: <74c5c8$ta7$1@nnrp1.dejanews.com>#1/1

Actually, depending on your jurisdiction, can hands/feet etc be classified as weapons (professional boxers, etc?)

A before insert or update trigger is the only answer. Something like: trigger header
ammo number;
begin

    ammo := 0;
   if :new.weapon_type='NONE' then

      select count(*) into ammo from arrest_table
      where arrest_id=:old.arrest_id
        and weapon_type <> 'NONE'.

   end if;
   if ammo > 0 then
     raise exception;
   end if;
end;
In article <36669F9B.ACE0F542_at_avionics.bfg.com>,   Michael Komlev <mike_komlev_at_avionics.bfg.com> wrote:
> Hi there,
> Hope it will help.
> Mike
>
> WEAPON_ID NUMBER(6) CONSTRAINT PK_WEAPON PRIMARY KEY,
> ARREST_ID NUMBER(6) CONSTRAINT FK_WEAPON REFERENCES ARREST,
> WEAPON_TYPE VARCHAR2(6)
>
> 1) Unique constraint on all three items:
>
> ALTER TABLE weapons ADD CONSTRAINT uk_weapon UNIQUE (weapon_id, arrest_id,
> weapon_type);
>
> 2) Add check constraint:
>
> ALTER TABLE weapons
> ADD CONSTRAINT chk_weapon
> CHECK (weapon_type = 'NONE' AND weapon_id = 0 OR weapon_type <> 'NONE');
>
> 3) Provide 0 for all insert or updates with weapon_type = 'NONE'.
>
> The values in the table could be:
>
> weapon_id arrest_id weapon_type
> ---------------------------------------
> 1 1 'GUN'
> 2 1 'KNIFE'
> 3 1 'PILLOW'
> 0 2 'NONE'
> 1 3 'M-GUN'
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sat Dec 05 1998 - 00:00:00 CST