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