db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Null values with conditions
Date Mon, 07 May 2012 12:36:21 GMT
On 5/7/12 5:14 AM, Biene_Majo@yahoo.de wrote:
> Hi,
>
> i have a table with 4 columns:
> A1
> A2
> B1
> B2
> i now need something like this (in relation to null values):
> (A1 AND A2) OR (B1 AND B2)
>
> So that if A1 is not null A2 must not be null and the other way around 
> (so if A2 is not null A1 is not allowed to be null).
> The same for B1 and B2.
> And at least one of the 2 pairs must not be null.
>
> Can someone tell me if this is possible or how?
>
>
> Thanks for any suggestions / answers
>
> Biene Majo
>
Hi Biene,

What you describe sounds like a CHECK constraint to me. Not sure that I 
understand the exact condition you're trying to catch, but something 
like the following might work for you:

create table t
(
     a1 int,
     a2 int,
     b1 int,
     b2 int,

     check
     (
         (
             ( a1 is not null and a2 is not null )
             or
             ( a1 is null and a2 is null )
         )
         and
         (
             ( b1 is not null and b2 is not null )
             or
             ( b1 is null and b2 is null )
         )
     )
);

Hope this helps,
-Rick

Mime
View raw message