SQL Server Table Logical Expressions

To put it simply, a check constraint is a Boolean expression that evaluates to TRUE or FALSE (a note about NULL in a second). When a row is inserted or updated each check constraint is evaluated. If any of the check constraints evaluate FALSE the transaction is rolled back. If all of the check constraints evaluate to any value other than FALSE the transaction continues. I say any other value because NULL introduces three-value logic into the mix. A NULL or unknown value will not cause the transaction to fail – only a check constraint that evaluates to FALSE will.

For example, say we had a table that was used by employees to track mileage to and from job sites. We can use the following check constraint to limit the maximum number of miles to 100:

miles <= 100 This statement will return true if miles (the column name) is less than or equal to 100. The <= part of the above check constraint is called a comparison operator and should be familiar to anyone who has done basic programming. SQL Server supports the following comparison operators and can be used on all data types except text, ntext, and image. Operators and their meanings: = Equal to > Greater than

< Less than >= Greater than or equal to

<= Less than or equal to <> Not equal to

!= Not equal to*

!< Not less than* !> Not greater than*

* Are not standard SQL comparison operators, but are implemented by SQL Server.

Another type of operator you can use are logical operators such as AND and OR. For example, we can add more constraints on the number of miles by using the AND operator. For example, lets add the restriction that each trip must be more than 0 miles:

miles > 0 AND miles <= 100 Our new check constraint now only allows miles to be in the range 1 to 100. So far we have looked at column level check constraints, that is, constraints that only involve one column. In addition to that kind of constraint, we can also write constraints that involve multiple columns from the *same* table and the *same* row - known as table level check constraints. The word same in the last sentence is very important, because check constraints cant reference columns from other tables, only the one they are created in. Additionally, a check constraint can only reference the current row in the table. For example, say we have a table that tracks customer address information with the columns country and region and we want to add the constraint that if that country is USA, region must contain a two letter state abbreviation. For this we can use the IN operator instead of having to type OR statements for each state abbreviation. (state abbreviations list shortened for readability) country <> ‘USA’ OR region IN (‘AL’,’FL’, ‘GA’)

There are at least a half dozen other ways you can write the above constraint that will accomplish the same thing.

Although understanding the basics of logical expressions are important, writing them is really more of a database programming issue rather than administration. There are many more operators you can use in your check constraints and I recommend checking out the topic operator precedence in Books Online if you are not used to writing logical expressions in SQL Server and want to learn more. Order of precedence and NULLS can cause unexpected results unless you have a good amount of experience.