Re: Constraint that compares and limits field values

>Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote on 01/25/2006 10:20:40
AM:
> On Wed, Jan 25, 2006 at 09:55:58AM -0800, MargaretGillon(at)chromalloy(dot)com
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