Constraints - Pro SQL Server Internals - page 155

Look at the example shown in Listing 7-7.
Listing 7-7. Check constaints: Table creation
create table dbo.Accounts
(
AccountId int not null identity(1,1),
AccountType varchar(32) not null,
CreditLimit money null,
constraint CHK_Accounts_AccountType
check (AccountType in ('Checking','Saving','Credit Card')),
constraint CHK_Accounts_CreditLimit_For_CC
check ((AccountType <> 'Credit Card') or (CreditLimit > 0))
)
There are two check constraints specified. The first one, CHK_Accounts_AccountType , enforces the rule that
AccountType needs to belong to one of the three values. The second one is more complex. It enforces the rule that, for
the Credit Card accounts, there should be a positive CreditLimit provided. One key point to remember is that data
is rejected only when a constraint expression is evaluated as FALSE. Null results are accepted. For example, the insert
statement shown in Listing 7-8 works just fine.
Listing 7-8. Check constaints: Inserting NULL value
insert into dbo.Accounts(AccountType, CreditLimit)
values('Credit Card',null)
The main purpose of check constraints is to enforce data integrity, although they can, in some cases, help Query
Optimizer and simplify execution plans. Assume that you have two tables: one that contains positive numbers and
another one that contains negative numbers, as shown in Listing 7-9.
Listing 7-9. Check constaints: PositiveNumbers and NegativeNumbers tables creation
create table dbo.PositiveNumbers
(
PositiveNumber int not null
);
create table dbo.NegativeNumbers
(
NegativeNumber int not null
);
insert into dbo.PositiveNumbers(PositiveNumber) values(1);
insert into dbo.NegativeNumbers(NegativeNumber) values(-1);
Now let's run the select that joins the data from those two tables. You can see the select statement in Listing 7-10
and the execution plan in Figure 7-7 .