Re: [GENERAL] Constraint that compares and limits field values

[MargaretGillon]

>Michael Fuhr wrote:
> ALTER TABLE event
>  ADD CONSTRAINT two_nulls_1 CHECK (((((evenid1 IS NULL) AND (evevid1 IS
> NULL)) OR ((evevid1 IS NULL) AND (evreid1 IS NULL))) OR ((evenid1 IS NULL)
> AND (evreid1 IS NULL))));
>
>Is there a requirement that exactly one column be NOT NULL?  If so
>then you'll need to add a check for that because this constraint
>would allow all three to be NULL.

You are correct, in each group of three columns, one needs to have an integer and the other two need to be NULL. So I need to modify the constraint to be....

ALTER TABLE event
ADD CONSTRAINT two_nulls_1 CHECK
((evenid1 IS NULL) AND (evevid1 IS NULL) and (evreid1 IS NOT NULL)) OR
((evenid1 IS NULL) AND (evevid1 IS NOT NULL) and (evreid1 IS NULL)) OR
((evenid1 IS NOT NULL) AND (evevid1 IS NULL) and (evreid1 IS  NULL))

Correct?
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297