How to: Use Check Constraints to Enforce Business Rules

As a developer, you may often find that it is essential to ensure data integrity before the data get into database. Otherwise, you will face a cleanup work or the situation of “trash in, trash out” afterwards. Today we will address how to enforce business rules by using check constraints on columns.

Here's an example of a Member table with name and phone number as columns. Let us check constraint on the phone number column. Constraint will suggest that phone numbers should follow the format of xxx-xxx-xxxx.

IF OBJECT_ID('[dbo].[Member]') IS NOT NULL
    DROP TABLE [dbo].[Member]
    CREATE TABLE [dbo].[Member](
    MEMBERID INT IDENTITY NOT NULL,
    [Name] varchar(25) NOT NULL,
    [PhoneNumber] varchar(12) NULL
    )

---- INSERT RECORDS
Insert INTO [dbo].[Member]    Values 
    ('Susan', '697-555-0142'),
    ('Kevin', '819-555-0175')

If you check the table, you can see it already has two records.

SELECT *
FROM  [dbo].[Member]

Add a CHECK CONSTRAINT

Then we add a CHECK CONSTRAINT to help check the format of phone numbers.

ALTER TABLE [dbo].[Member]
ADD CONSTRAINT CK_Member_Phone
CHECK ([PhoneNumber] LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
GO

If any of existing records in tables violate the constraint to be added, it will pop up messages like this:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint “CK_Member_Phone”. The conflict occurred in database "xxxx", table "dbo.Member", column 'PhoneNumber'.

 

Test a CHECK CONSTRAINT

Next, you can test the check constraint by trying to insert a record that has a phone number not following the format defined in the constraint CK_Member_Phone.

Insert INTO [dbo].[Member]    Values 
    ('Charles', '212-555-0187'),
    ('Chris', '612555-0100')

An error message will be returned like the following one:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Member_Phone". The conflict occurred in database "DemoSQL2012", table "dbo.Member", column 'PhoneNumber'.
The statement has been terminated.