SQL question: How to prevent mutually exclusive values?

I have the following table:

WEAPON_ID         NUMBER(6)  CONSTRAINT PK_WEAPON PRIMARY KEY,
ARREST_ID           NUMBER(6)  CONSTRAINT FK_WEAPON REFERENCES ARREST,
WEAPON_TYPE    VARCHAR2(6)

One of the valid values for weapon_type is 'None'. One of the business rules states: For a given arrest_id, more than one row can be entered, unless the weapon_type is 'None'. If the weapon type is 'None', it must be the only row for that arrest_id. In other words, for a given arrest_id, 'None' is mutually exclusive with any other value.

How do I enforce this requirement? Using an integrity constraint would be the simplest and easiest method, but I don't think that is possible in this instance. The Oracle Application Developer's Guide says that "complex business rules" (i.e., business rules that cannot be enforced with integrity constraints) require the use of database triggers. It sounds to me like a before-insert trigger is what I need to use. Is this true, or is there a better/easier way?

Thanks in advance for any suggestions.

Todd Owers
toddo_at_gcr1.com Received on Wed Dec 02 1998 - 00:00:00 CST