Professional SQL Server™ 2005 Programming

5.4. CHECK Constraints

The nice thing about CHECK constraints is that they are not restricted to a particular column. They can be related to a column, but they can also be essentially table related in that they can check one column against another as long as all the columns are within a single table, and the values are for the same row being updated or inserted. They may also check that any combination of column values meets a criterion.

The constraint is defined using the same rules that you would use in a WHERE clause. The following table gives examples of the criteria for a CHECK constraint:

Goal	SQL
Limit Month column to appropriate numbers	BETWEEN 1 AND 12
Proper SSN formatting	LIKE '[0-9][0-9][0-9]-[0-9] [0-9]-[0-9][0-9][0-9][0-9]'
Limit to a specific list of Shippers	IN ('UPS', 'Fed Ex', 'USPS')
Price must be positive	UnitPrice >= 0
Referencing another column in the same row	ShipDate >= OrderDate

This table only scratches the surface, and the possibilities are virtually endless. Almost anything you could put in a WHERE clause you can also put in your constraint. What's more, CHECK constraints are very fast performance-wise compared to the alternatives (rules and triggers).

Still building on your Accounting database, let's digress a moment and add a column to keeptrack of the date a customer is added to the system.

ALTER TABLE Customers
ADD DateInSystem datetime

With that in place, we're ready to add an additional modification to your Customers table to check for a valid date in your ...