Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enforcing business rules using SQL Server CHECK constraints


By:   |   Last Updated: 2008-12-19   |   Comments (6)   |   Related Tips: More > 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 as SQL Server MVP David Portas points out in this blog entry 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


Last Updated: 2008-12-19


next webcast button


next tip button



About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, August 17, 2015 - 5:07:55 PM - TheSmileyCoder // Anders Ebro Back To Top

Just wanted to say thank you. This helped me out with a problem today.


Wednesday, June 05, 2013 - 11:48:03 AM - Jim Back To Top

THANKS!!!  this helped me.

Jim


Tuesday, February 19, 2013 - 4:36:59 PM - Becky Back To Top

Thanks, this helped a lot!


Wednesday, June 16, 2010 - 10:03:50 AM - nosnetrom Back To Top
Good one! :-)

Friday, December 19, 2008 - 2:56:36 PM - aprato Back To Top

Sure, that's why I mentioned that you'd likely have your own variation.  The point wasn't to shave off 30 milliseconds here or there.  That's something you experiment with and can tweak on your own.


Friday, December 19, 2008 - 11:55:52 AM - jerryhung Back To Top

 Useful post, learned something new about the TRUE/FALSE (1/0)...

I suppose using COALESCE or ISNULL(IS_MANAGER) may be quicker, but that's not the point anyway

CHECK (CASE WHEN COALESCE(IS_MANAGER,0) <> 1 AND BONUSPCT >= 5.00 THEN 1
            ELSE 0 END = 0)


Learn more about SQL Server tools