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:
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
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