db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From José Ventura <st.ne...@gmail.com>
Subject Re: Null values with conditions
Date Mon, 07 May 2012 13:03:35 GMT
I think the suggested answer will allow all values to be null. If at least
one pair must not be null, we can add the following:

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 )
    )
    *and ( a1 is not null or b1 is not null )   *
)

(we can test only a1/b1 here because the "other member of the pair" has
already been tested earlier).

On Mon, May 7, 2012 at 9:36 AM, Rick Hillegas <rick.hillegas@oracle.com>wrote:

> 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