Re: Constraint that compares and limits field values

[MargaretGillon@xxxxxxxxxxxxxx]

>Martijn van Oosterhout <kleptog@xxxxxxxxx>
wrote on 01/25/2006 10:20:40 AM:
> On Wed, Jan 25, 2006 at 09:55:58AM -0800, MargaretGillon@xxxxxxxxxxxxxx
wrote:
> 
> Parhaps something like:
> 
> CHECK((CASE WHEN evenid1 IS NOT NULL THEN 1 ELSE 0 END) + 
>       (CASE WHEN evenid2 IS NOT NULL THEN 1 ELSE 0
END) +
>       (CASE WHEN evenid3 IS NOT NULL THEN 1 ELSE 0
END)) = 1;
> 

This works with a few modifications... needed an extra
( ) enclosing entire statement. 

ALTER TABLE event
ADD CONSTRAINT two_nulls_1  
CHECK (((CASE WHEN evenid1 IS NOT NULL THEN 1 ELSE
0 END) + 
      (CASE WHEN evevid1 IS NOT NULL
THEN 1 ELSE 0 END) +
      (CASE WHEN evreid1 IS NOT NULL
THEN 1 ELSE 0 END)) = 1);

> If you can find a function to turn a bool into
an int it becomes even
> easier.

I am in version 7.3 and it will not let me cast the
boolean to an integer.

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