CREATING THE PHYSICAL MODEL WITH SQL SERVER - A Developer's Guide to Data Modeling for SQL Server - page 213

happen, and your database needs to be prepared to stand on its own to
maintain data integrity.
Check Constraints
Check constraints are a wonderful tool to help you ensure that the data
being loaded into your database is what you expect and require. Using
some custom code, you can force data to conform to almost any pattern
your heart desires. To check data, you use an expression to look at the data
and compare it to some desired result. For example, to ensure that all val-
ues in a salary column are greater than or equal to the minimum wage
($5.85, in this example), your expression would be
salary >= 5.85
Let's look at an example from the Mountain View Music database. As
you will recall, we created a table named tbl_customer that holds all the
data pertinent Mountain View's customers. The table has three columns
for phone numbers called homephone, workphone, and mobilephone.
Each of these columns allows NULL values, and this means that a cus-
tomer could conceivably choose not to provide a phone number at all. The
company has decided that it needs to have at least one phone number that
it can use to call the customer in the event of a problem with an order. To
that end, you need to place a check constraint on tbl_customer to ensure
that at least one phone number is provided. Here is the simple expression
to check for this condition.
([homephone] IS NOT NULL
OR [workphone] IS NOT NULL
OR [mobilephone] IS NOT NULL)
This expression, when used in a check constraint, forces one of the
phone numbers to be supplied. Failure to supply at least one phone num-
ber causes your insert or update statement to fail. To add a constraint to
tbl_customer with the expression you use the following code.
ALTER TABLE dbo.tbl_customer WITH CHECK
ADD CONSTRAINT CK_phone_number
CHECK (([homephone] IS NOT NULL
OR [workphone] IS NOT NULL
OR [mobilephone] IS NOT NULL))