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

[MargaretGillon]

>Martijn van Oosterhout <kleptog@svana.org> wrote on 01/25/2006 10:20:40 AM:
> On Wed, Jan 25, 2006 at 09:55:58AM -0800, [EMAIL PROTECTED] 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