SQL server check for null value

Question

I have a table and I am adding a constraint so if PaymentType is different than 'Check' to ensure that the CheckNumber is null. This is my table:

create table RegistrationHeader(
    RegistrationNo numeric 
    ,BillingID varchar(30) unique not null
    ,RegistrationDate date not null
    ,PaymentType varchar(5) check (PaymentType = 'CC' or PaymentType = 'PO' or PaymentType = 'Check') not null
    ,CCNumber varchar(16) check(LEN(CCNumber) = 16 OR LEN(CCNumber) = 15)
    ,PONumber varchar(30)
    ,CheckNumber varchar(10) default null
    ,primary key(RegistrationNo)
    ,constraint CC_CCNumber_constr check(
        (PaymentType = 'CC' and CCNumber is not null)
        or
        (PaymentType != 'CC' and CCNumber is null)
    )
    ,constraint PO_PONumber_constr check(
        (PaymentType = 'PO' and (PONumber is not null or PONumber != ''))
        or
        (PaymentType != 'PO' and PONumber is null)
    )
);

So, I have tried

,constraint CheckNumber_type_constr check((PaymentType = 'CC' or PaymentType = 'PO') and CheckNumber is null)

and

,constraint CheckNumber_type_constr check((PaymentType != 'Check') and CheckNumber is null)

and both fail miserably...

Gordon Linoff · Accepted Answer · 2014-01-21 01:01:13Z

1

How about:

constraint CheckNumber_type_constr
    check ((PaymentType = 'Check') or (CheckNumber is null))

If you want to ensure that CheckNumber not null for checks as well:

constraint CheckNumber_type_constr
    check ((PaymentType = 'Check' and CheckNumber is not null) or
           (PaymentType <> 'Check' and CheckNumber is null)
          )

answered Jan 21, 2014 at 1:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL server check for null value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related