Re: SQL question: How to prevent mutually exclusive values?
From: Michael Komlev <mike_komlev_at_avionics.bfg.com>
Date: 1998/12/03
Message-ID: <36669F9B.ACE0F542_at_avionics.bfg.com>#1/1
Date: 1998/12/03
Message-ID: <36669F9B.ACE0F542_at_avionics.bfg.com>#1/1
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)
- 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'Received on Thu Dec 03 1998 - 00:00:00 CET