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

Hi there,
Hope it will help.
Mike

[Quoted] 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'