Re: Constraint that compares and limits field values

[MargaretGillon@xxxxxxxxxxxxxx]

>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