Data restriction using CHECK constraints

How to use the CHECK command to verify formats as well as ranges and matches.

You can use one or more CHECK constraints to limit the data that can be entered into your tables or column. A CHECK

evaluates a Boolean equation and determines if the data either matches or doesn't match the condition. While a CHECK constraint doesn't provide the programming logic and functionality of either triggers or stored procedures, it's easy to write and it runs quickly.

To add a CHECK constraint for a table, you would place the condition at the bottom of the ALTER/CREATE TABLE statement. A column CHECK constraint is simply embedded into the statement itself. For example, to limit your table to allow only transactions below someone's credit limit you might enter CREDITLIM – TRANSAMT >= 0 you would add that statement to the bottom. For restriction of values to only zip codes in the 02052 area code you would use the statement ZIPCODE = 02052 and use that statement as part of the column description. So far this is standard stuff.

But CHECKS are not only useful for matches and ranges, but they can be used to evaluate patterns and formats. For example, you can make sure that a zip codes is only numeric numbers by including a formula such as: ZIPCODE LIKE '[0-9][0-9][0-9][0-9][0-9]. To ensure that a position is an alphabetic value you would use the value [A-Z]. You can also limit the set of values into a column using a statement similar to this: ZIPCODE IN ('02052', '02054', '02059'), which functions like a radio button group on a form.

Thus a TABLE CHECK can be defined separately from any particular column, and the results can be used to populate data in one or more columns. For any constraint populating two or more columns, this is by definition a table constraint. So consider a table definition that creates a lookup table. It might be structured as follows:

CREATE TABLE ZIPCODES
(ZIPCODE NUMERIC,
CITY CHAR,
STATE CHAR,
CONSTRAINT StateForm CHECK STATE LIKE '[A-Z][A-Z]',
ZIPCODE LIKE '[0-9][0-9][0-9][0-9][0-9]
ZIPCODE ((>=01001 AND <= 02791) AND (>=05501 and <=05544)) AND STATE = 'MA'

The above definition creates a column restraint ensuring that only a five number string can be entered into the ZIPCODE field and only two letters can be entered in the STATE field, both formatting restraints. The last line ensures that only the zip codes specified in the state of MA that correctly have the right range of zip codes will appear in your lookup table. The two ranges are required to ensure that zip codes in Andover for the IRS are also included.


Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.


This was first published in March 2005

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close