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 <[email protected]>wrote:
> On 5/7/12 5:14 AM, [email protected] 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
>
