CODEWITHSUNDEEP
sqlsql-server
setlio
setlio

Reputation: 726

SQL Server restriction between columns in one table

I have the following table:

 create table Person(
    FName varchar(15) not null
    ,LName varchar(25) not null
    ,Address varchar(100)
    ,City varchar(30)
    ,State varchar(30)
    ,Zip varchar(10)
    ,Phone varchar(15)
    ,Email varchar(60) unique
    ,UserID varchar(30)
    ,Password varchar(30) not null
    ,CCtype varchar(8)
    ,CCNumber varchar(16)
    ,primary key(UserID)        
);

And I want to write a restriction where if there is CCNumber, the CCType can not be empty. The other way around is fine. If I have type I may not have a number, and that is ok. I have tried those constraints but both are not working as expected when i test.

,constraint CCNumber_no_CCType check((CCNumber is null) or (CCType is null))

or

,constraint CCNumber_no_CCType check((CCNumber is not null) and (CCType is null))

Upvotes: 2

Views: 85

Answers (4)

Zzyrk
Zzyrk

Reputation: 905

constraint CCNumber_no_CCType(CCNumber = '' OR (CCNumber <> '' AND CCType IS NOT NULL))

Upvotes: 1

Lajos Veres
Lajos Veres

Reputation: 13725

Try to add a "not" to your first trial's second part:

,constraint CCNumber_no_CCType check((CCNumber is null) or (CCType is not null))

Upvotes: 1

StuartLC
StuartLC

Reputation: 107347

Try like so:

constraint CCNumber_no_CCType check((CCNumber is null) 
         or (CCNumber is not null and CCType is not null))

As others have pointed out, K map simplification should mean that the or (CCNumber is not null .. becomes redundant.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270633

The constraint that you want is:

constraint CCNumber_no_CCType check((CCNumber is null) or (CCType is not null))

Here is a more general way to think of this. If you have "if A --> B", then this is true when either B is true or not A is true.

Upvotes: 4

Related Questions