Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using Check Constraints

By Robert Marda,

Useful Check Constraints

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.

Total article views: 11065 | Views in the last 30 days: 18
 
Related Articles
SCRIPT

Fix Column Check Constraint Naming

This stored procedure can be used for applying a custom column check constraint naming convention

FORUM

Check Constraint with Case Statement

Check Constraint with Case Statement

SCRIPT

Conversion of rule objects to column check constraints

This script can convert the usage of bound rule objects in tables to column check constraints

FORUM

CHECK constraint with Parameterised UDF

i have a check constraint with parameteried udf and failing to understand an update behaviour

FORUM

Creating a Check Constraint

Check Constraint

Tags
administration    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones