Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL question: How to prevent mutually exclusive values?
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)
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 CST