PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 67

salary >= 10000 and salary <=150000
This line rejects any value less than 10,000 or greater than 150,000.
Each column can have multiple check constraints, or you can refer-
ence multiple columns with a single check. When it comes to NULL val-
ues, check constraints can be overridden. When a check constraint does its
evaluation, it allows any value that does not evaluate to false. This means
that if your check evaluates to NULL, the value will be accepted. Thus, if
you enter NULL into the salary column, the check constraint returns un-
known and the value is inserted. This feature is by design, but it can lead
to unexpected results, so we want you to be aware of this.
Check constraints are created in much the same way as keys or unique
constraints; the only caveat is that they tend to contain a bit more meat.
That is, the expression used to evaluate the check can be lengthy and
therefore hard to read when viewed in T-SQL. We recommend you create
your tables first and then issue ALTER statements to add your check con-
straints. The following sample code adds a constraint to the Products table
to ensure that certain columns do not contain negative values.
ALTER TABLE dbo.Products
ADD CONSTRAINT chk_non_negative_values
CHECK
(
weight >= 0
AND (shippingweight >= 0 AND shippingweight >= weight)
AND height >= 0
AND width >= 0
AND depth >= 0
)
Because it doesn't make sense for any of these columns to contain neg-
ative numbers (items cannot have negative weights or heights), we add this
constraint to ensure data integrity. Now when you attempt to insert data
with negative numbers, SQL Server simply returns the following error and
the insert is denied. This constraint also prevents a shipping weight from
being less than the product's actual weight.
The INSERT statement conflicted with the CHECK constraint
"chk_non_negative_values"
As you can see, we created one constraint that looks at all the columns
that must contain non-negative values. The only downfall to this method is