Using Check Constraints

 

Introduction

 

Check constraints can be added to a table to enforce specific rules on one or more columns.  By using check constraints you can specify a valid range of values for a specific column or group of columns.  When placing check constraints you should always have it check to ensure that all existing values are valid.  By default when you add or create a check constraint it will check all existing values unless you use the keywords WITH NOCHECK.

 

One column can have multiple check constraints.  If more than one check constraint exists for a column SQL Server will check them in the order they were created.  Check constraints are useful to enforce business rules at all times, especially from manual data changes made in Query Analyzer or Enterprise Manager.  Many constraints are enforced by a client application prior to sending queries that modify data to the database.  This technique avoids unneeded network traffic when data does not meet required constraints.  Having constraints on specific columns in SQL Server simply ensures that everything complies with the same rules.

 

Now that I have described check constraints, I will show you some useful constraints that you can use in your databases.  I will use the pubs and Northwind databases for my  examples.  I developed and tested the examples in this article on SQL Server 2000.  I think they will work on SQL Server 7.0, but do not have one to test them on.

 

Example 1:  Phone Numbers

 

Execute the following code in Query Analyzer:

 

USE pubs

 

ALTER TABLE authors

ADD CONSTRAINT cc_ValidPhone

CHECK (Phone LIKE '[0-9][0-9][0-9] [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

 

Now try to modify any phone number in the authors table.  You can try changing a number to a letter or try changing the space after the third number to a dash.  The above constraint will block these changes and display an error message for you that will read something like this:

 

Server: Msg 547, Level 16, State 1, Line 1

UPDATE statement conflicted with COLUMN CHECK constraint 'cc_ValidPhone'. The conflict occurred in database 'pubs', table 'authors', column 'phone'.

The statement has been terminated.

 

You can modify the constraint to also accept an alternate format as follows:

 

ALTER TABLE authors

DROP CONSTRAINT cc_ValidPhone

 

ALTER TABLE authors

ADD CONSTRAINT cc_ValidPhone

CHECK (Phone LIKE '[0-9][0-9][0-9] [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

OR Phone LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

 

This constraint would allow either format for a phone number.  Please note that SQL Server will let us add this constraint even though no phone number using the second phone format can ever be added unless we increase the length of the Phone column from 12 to 15.

 

Example 2:  Birth Dates

 

The following code will place a check constraint on the employees table in the Northwind database:

 

USE Northwind

 

ALTER TABLE employees

ADD CONSTRAINT cc_ValidBirthDate

CHECK (BirthDate LIKE '%19[0-9][0-9]%')

 

This constraint ensures that the birth year is at least 1900 and will prohibit all other birth years.  Now, assuming this article survives long enough to make it necessary to allow some birth years in the early 2000’s you could modify the constraint as follows:

 

ALTER TABLE employees

DROP CONSTRAINT cc_ValidBirthDate

 

ALTER TABLE employees

ADD CONSTRAINT cc_ValidBirthDate

CHECK (BirthDate LIKE '%19[0-9][0-9]%' OR BirthDate LIKE '%200[0-2]%')

 

This constraint will allow years from 1900 to 2002.

 

Example 3:  Age

 

The below constraint will make so that only certain ages can be entered and will prohibit all others.  For this example we will add a column to the employees table in the Northwind database.  You can execute the following code in Query Analyzer:

USE Northwind

 

ALTER TABLE employees

ADD Age tinyint

 

ALTER TABLE employees

ADD CONSTRAINT cc_ValidAge

CHECK (Age > 18 AND Age < 65)

 

UPDATE employees SET Age = 64 WHERE employeeID = 1

UPDATE employees SET Age = 19 WHERE employeeID = 1

 

UPDATE employees SET Age = 18 WHERE employeeID = 1

UPDATE employees SET Age = 65 WHERE employeeID = 1

 

You will notice that the first two updates will work fine and the second two updates will be blocked by the constraint.

 

Example 4:  Multiple Columns

 

We can also create constraints that effect more than one column.  The following sample code will create a constraint that uses two columns from the employees table:

 

USE Northwind

 

ALTER TABLE employees

ADD CONSTRAINT cc_ValidEmployment

CHECK (DATEDIFF(yyyy, BirthDate, HireDate) > 17)

 

Now when you execute the below updates they will be blocked because they violate the constraint.

 

UPDATE employees SET BirthDate = '1998-12-08' WHERE EmployeeID = 1

UPDATE employees SET HireDate = '1948-12-08' WHERE EmployeeID = 1

UPDATE employees SET BirthDate = '1998-12-08', HireDate = '1948-12-08' WHERE EmployeeID = 1

 

Conclusion

 

Constraints are useful to help maintain database integrity, especially from updates done via Enterprise Manager or Query Analyzer.  Constraints aren’t needed if you always validate data on the client side before sending update requests to SQL Server.  Having constraints simply ensures that nothing gets past the client application and makes sure DBA’s and SQL programmers don’t accidentally enter an invalid value somewhere.