By: Armando Prato | Updated: 2008-12-19 | Comments (7) | Related: > Constraints
Problem
I am designing a table and I am trying to enforce domain integrity on some of my columns using CHECK constraints. Can you give me some examples of using CHECK constraints to constrain a single column and to constrain a column based on the value in another column?
Solution
If you're not familiar with CHECK constraints, they're used to enforce column domain integrity by limiting the values that may be inserted into a column. I find that CHECK constraints are woefully under used in most data models. In a lot of cases, I've seen a trigger used to enforce a business rule that could have been enforced using a CHECK. I personally use CHECKs over triggers for a variety of reasons. For one reason, I don't need to waste time to write specialized code. Another reason is that CHECK constraints are enforced before a database modification is made by the database engine whereas a trigger is checked after the fact. Using triggers extends the life of a transaction and can potentially be expensive if a ROLLBACK condition is detected.
SQL Server allows you define column level CHECK constraints which check the integrity of a single column. Furthermore, SQL Server allows you to check the values of multiple columns together using a "table level" CHECK constraint. Though they're labeled as "table level" constraints, these type of CHECK constraints are actually checked at the row level. Lastly, CHECK constraints work by examining a defined condition to see if it evaluates to either TRUE or FALSE.
Let's move on to some examples. I'll set up a sample table called EMPLOYEE. As part of our table definition, we'll enforce a company rule that no employee in our system is allowed to earn more than $100,000 or less than $30,000.
CREATE TABLE DBO.EMPLOYEE ( EMPLOYEEID INT IDENTITY(1,1) NOT NULL, FIRSTNAME VARCHAR(50) NOT NULL, LASTNAME VARCHAR(50) NOT NULL, IS_MANAGER BIT NULL, SALARY MONEY NOT NULL, BONUSPCT FLOAT NOT NULL ) GO ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEEID) GO ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT DF_EMPLOYEE_BONUSPCT DEFAULT 0.00 FOR BONUSPCT GO ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT CK_EMPLOYEE_SALARY CHECK (SALARY BETWEEN 30000 AND 100000) GO EXEC SP_HELPCONSTRAINT EMPLOYEE GO
Examining the constraints in our table, we see our simple, column level constraint defined:
Trying to insert values into the SALARY column that fall outside our defined range are successfully caught by the database engine.
INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, SALARY) SELECT 'GEORGE', 'WASHINGTON', 110000 GO
So far, so good. However, there's another company rule that we need to enforce. Managers in our company can earn any bonus percentage but they are the only employee type that is allowed to earn a bonus of 5 or more percent of his/her salary. Non-managers can earn any amount provided it's less than 5%. Let's try to enforce this company rule via a table CHECK constraint:
ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT CHECK (IS_MANAGER = 1 AND BONUSPCT >= 5.00) GO INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT) SELECT 'GEORGE', 'WASHINGTON', 1, 100000, 5.00 GO INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT) SELECT 'BEN', 'FRANKLIN', 0, 75000, 2.50 GO
What happened? Employee Washington was inserted ok but Franklin failed. Well, the CHECK constraint as it's defined forces all employees to be both a manager and to have a bonus percentage greater than or equal to 5. That's great if all employees must be managers with a bonus of 5% or greater. However, that's not the business rule we're trying to implement. We're trying to make sure that if the employee is not a manager, he/she cannot exceed the 5% bonus threshold. So how can we have the CHECK constraint enforce integrity conditionally? You might get tempted to use a trigger. However, the CHECK constraint supports CASE expressions. Furthermore, as I mentioned at the beginning of this tip, CHECKs are examined for TRUE/FALSE conditions. As a result, you can leverage these two together to produce a CHECK constraint that can perform conditional checking. Let's re-create our CHECK using these principles and try to re-insert employee Franklin.
ALTER TABLE DBO.EMPLOYEE DROP CONSTRAINT CK_EMPLOYEE_BONUSPCT GO ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT CHECK (CASE WHEN IS_MANAGER <> 1 AND BONUSPCT >= 5.00 THEN 1 ELSE 0 END = 0) GO INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT) SELECT 'BEN', 'FRANKLIN', 0, 75000, 2.50 GO
Now trying to INSERT Franklin with the corrected CHECK constraint definition results in success.
Now let's try to INSERT a new employee, Jefferson.
INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT) SELECT 'THOMAS', 'JEFFERSON', NULL, 80000, 7.50 GO
Wait a second, Jefferson was not flagged as a manager but the engine allowed his bonus of 7.5%! Why did this happen? Again, recall that CHECK constraints are examined for TRUE/FALSE conditions. Looking at our schema, the IS_MANAGER column is declared as NULL. Any NULL values in this column will cause the IS_MANAGER condition in the CHECK to equate to unknown and cause the CASE expression to evaluate to our default boolean value for success (zero). When working with nullable columns, this is a gotcha to be aware about. There are a couple of ways to correct this. One is to define the IS_MANAGER flag as NOT NULL, converting the NULL values to zero. If you cannot change the model, the other is to re-write the CASE to account for NULL IS_MANAGER flags. Below is one way to re-write the CASE (you'll probably have your own variation; my version is for illustrative purposes).
TRUNCATE TABLE DBO.EMPLOYEE GO ALTER TABLE DBO.EMPLOYEE DROP CONSTRAINT CK_EMPLOYEE_BONUSPCT GO ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT CHECK (CASE WHEN IS_MANAGER IS NULL AND BONUSPCT >= 5.00 THEN 1 WHEN IS_MANAGER <> 1 AND BONUSPCT >= 5.00 THEN 1 ELSE 0 END = 0) GO INSERT INTO DBO.EMPLOYEE (FIRSTNAME, LASTNAME, IS_MANAGER, SALARY, BONUSPCT) SELECT 'JAMES', 'MADISON', NULL, 60000, 5.50 GO
Executing this script for a new employee, Madison, we see that the engine now successfully captures this condition
Now you're probably thinking "I bet I could write a scalar function to read my entire table and perform a true table level CHECK to check for sums and aggregates being exceeded". You're right, you could, but there are risks involved.
As you can see, CHECK constraints are a powerful way to enforce single and multi-column domain integrity without needing to write specialized triggers or stored procedure code.
Next Steps
- Read more about the CHECK constraint in the SQL Server 2005 Books Online
- Read this tip about Using the CASE Expression Instead of Dynamic SQL
About the author
Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2008-12-19
