In this post we will look at some examples of check constraints in SQL Server. We will start with a table and we will add some values to the table and some check constraints. Check constraints allow you to refine the data requirements beyond the data type. For example if you have an integer data type but you only wanted the numbers to be within a certain range. You might have a date data type but you don’t want any dates in the past to be entered into the table. You can use check constraints. Another example would be the entry of Canadian postal codes where you have a char data type of length 6 but you need to have the data in the correct format (alpha, numeric, alpha, numeric, alpha and numeric). In an example below we have a Currency column that can only accept “CAD”.

Below is a script for creating a table with four columns. We will add a row of valid data to the table and then we will add our constraints. After that we can test our table by attempting to add values that are not valid and expect SQL Server to complain to us.

CREATE TABLE [dbo].[Examples2](
	[CurrencyMustBeCAD] [char](3) NULL,
	[AlphaAlphaDigitDigit] [char](4) NULL,
	[EitherABCorD] [char](1) NULL,
	[TenDigitPhoneNoPunctuation] [char](10) NULL
) ON [PRIMARY]
GO

Now we will add some valid data to the table.

INSERT INTO Examples2 
VALUES ('CAD','ag34', 'B', 4165559999);

Adding Constraints to a Table

You can simple script the constraint in a new query window as shown below.

alter table Examples2
add constraint chkCurrencyMustBeCAD check (CurrencyMustBeCAD = 'CAD');

If your database name is [Constraints] and you then script the table (right click the table in SSMS) with Script Table as… Create to… you will get the following result. (I removed some of the blank rows so it takes less space).

USE [Constraints]
GO
CREATE TABLE [dbo].[Examples2](
	[CurrencyMustBeCAD] [char](3) NULL,
	[AlphaAlphaDigitDigit] [char](4) NULL,
	[EitherABCorD] [char](1) NULL,
	[TenDigitPhoneNoPunctuation] [char](10) NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Examples2]  WITH CHECK ADD  CONSTRAINT [chkCurrencyMustBeCAD] CHECK  (([CurrencyMustBeCAD]='CAD'))
GO
ALTER TABLE [dbo].[Examples2] CHECK CONSTRAINT [chkCurrencyMustBeCAD]
GO

Here is another example.

alter table Examples2
add constraint chkAlphaAlphaDigitDigit check (AlphaAlphaDigitDigit LIKE '[a-z][a-z][0-9][0-9]');

If you try to insert a row that violates one or more of the check constraints, you get an error. For example if you try to run the code below you get an error. The check constraint chkAlphaAlphaDigitDigit requires two letters followed by two numerical digits.

insert into Examples2 
values ('CAD', 'q2k9', 'C', '4165551212');

Below is the error I got, and the row was not inserted.

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkAlphaAlphaDigitDigit". 
The conflict occurred in database "Constraints", table "dbo.Examples2", column 'AlphaAlphaDigitDigit'.
The statement has been terminated.

Also, the check constraint chkAlphaAlphaDigitDigit DOES accept capital letters in the first two positions.

Drop Constraint

You can drop a constraint if you know the table name and the name of the constraint.

alter table Examples2
drop constraint chkCurrencyMustBeCAD;

Only for New Rows

Suppose your table is full of data. You need to add a new constraint, but only for new rows. In other words you do not want the constraint to check all of the existing rows to see if they violate the constraint because some of the rows actually do violate the constraint and you need the constraint to be accepted by SQL Server for all new incoming rows of data. How do you do that? You use the expression with nocheck. It may seem odd that you are adding a check and also using the no check expression, but think of the no check as applying to the table’s existing data.

alter table Examples2 with nocheck
add constraint chkCurrencyMustBeCAD check (CurrencyMustBeCAD = 'CAD');

Scripting, Adding and Modifying Constraints

You can use the New Query window and write your own code, or you can use the Object Explorer, as shown in the diagram below.

If you double-click the constraint chkCurrencyMustBeCAD in Object Explorer you get the window shown below.