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

Hi

Todd Owers wrote in message <743vnr$c9g$1_at_nntp.gulfsouth.verio.net>...
>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?

Yes, you need triggers. But only FOR STATEMENT trigger can check you rule directly. Moreover, to get efficient solution you must write chain of triggers
BEFORE... STATEMENT - BEFORE .. FOR EACH ROW - AFTER...STATEMENT + package to save list of records.

Alternate way - to add column W_UNIQ and set it to decode(weapon_type,'None',NULL,weapon_id). You can do it in simple trigger FOR EACH ROW. Then constraint UNIQUE(arrest_id,w_uniq) will enforce your rule.

By

Leon Grinberg leon_at_grant.kharkov.ua Received on Thu Dec 10 1998 - 00:00:00 CST