Check entry based on entry in another column Still fairly new to databases and SQL, so bear with me. I'm trying to check whether or not data can be inserted depending on data in another column,

create table Table1
       (PaymentType varchar(5) not null
       check (PaymentType = 'CC' or PaymentType = 'PO' or PaymentType = 'Check'),
       CCNumber varchar(16)
       check (PaymentType = 'CC' and (LEN(CCNumber) = 15 or LEN(CCNumber) = 16) ),
       PONumber varchar(30)
       check (PaymentType = 'PO'),
       CheckNumber varchar(10)
       check (PaymentType = 'Check') );


This probably isn't the way to go about it, but I want CCNumber to accept data if PaymentType is 'CC', PONumber if PaymentType is 'PO', etc. How would I create those checks or constraints properly?
One way would be a check constraint, but this will not give meaningful error messages

create table Table1
 (PaymentType varchar(5) not null,
 CCNumber varchar(16),
 PONumber varchar(30),
 CheckNumber varchar(10));
 GO
 ALTER Table Table1
 add CONSTRAINT CHK_PAYMENTTYPE CHECK 
  (CASE WHEN PaymentType = 'CC' and CCNumber is NOT NULL and PONumber is NULL and CheckNumber is NULL THEN 0
    WHEN PaymentType = 'PO' AND PONumber IS NOT NULL and CCNumber IS NULL and CheckNumber is NULL THEN 0
    WHEN PaymentType = 'Check' AND CheckNumber IS NOT NULL and CCNumber IS NULL and PONumber is NULL THEN 0
    ELSE 1 END = 0);


It would probably be better to do a foreign key relationship or just have a single column for the number with a check constraint that checks that the number is of correct format for each payment type

ps: dont store CreditCard numbers unencrypted in a database...