A couple of months back we were interviewing people for 2 positions, one of the questions I like to ask is the following: If you have a column in a table that’s an integer data type how can you restrict the values to be between 1 and 10? Most of the people start by saying that they restrict it in the application, when I ask how they would prevent from someone who has write access to do it they usually say that they would add a trigger. Only about 20% of the people know that there is something in the table designer where they can enter a range. Between 5 and 10% of the people know that this is called a check constraint. If you know what a check constraint is…bravo, you my friend are an elitist!

I forgot about these interviews but this question Overriding the maximum value of a bigint datatype in MSSQL made it reappear like a phoenix that rises from the ashes.

Let’s look at some examples

First create this table

T-SQL
1
2
create table SomeTable(code char(3) not null)
GO
create table SomeTable(code char(3) not null)
GO

Now let’s say we want to restrict the values that you can insert to only accept characters from a through z, here is what the constraint looks like

T-SQL
1
2
3
alter table SomeTable add  constraint ck_bla
check (code like '[a-Z][a-Z][a-Z]' )
GO
alter table SomeTable add  constraint ck_bla
check (code like '[a-Z][a-Z][a-Z]' )
GO

If you now run the following insert statement….

T-SQL
1
insert SomeTable values ('123')
insert SomeTable values ('123')

You get this error message back

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint “ck_bla”. The conflict occurred in database “tempdb”, table “dbo.SomeTable”, column ‘code’.
The statement has been terminated.

What if you have a tinyint column but you want to make sure that values are less then 100? Easy as well, first create this table

T-SQL
1
2
create table SomeTable2(SomeCol tinyint not null)
GO
create table SomeTable2(SomeCol tinyint not null)
GO

Now add this constraint

T-SQL
1
2
3
alter table SomeTable2 add  constraint ck_SomeTable2
check (SomeCol < 100 )
GO
alter table SomeTable2 add  constraint ck_SomeTable2
check (SomeCol < 100 )
GO

Try to insert the value 100

T-SQL
1
insert SomeTable2 values ('100')
insert SomeTable2 values ('100')

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint “ck_SomeTable2”. The conflict occurred in database “tempdb”, table “dbo.SomeTable2”, column ‘SomeCol’.
The statement has been terminated.

Okay, what happens if you try to insert -1?

T-SQL
1
insert SomeTable2 values ('-1')
insert SomeTable2 values ('-1')

Msg 244, Level 16, State 1, Line 1
The conversion of the varchar value ‘-1’ overflowed an INT1 column. Use a larger integer column.
The statement has been terminated.

As you can see you also get an error, however this is not from the constraint but the error is raised because the tinyint datatype can’t be less than 0

Check constraint can also be tied to a user defined function and you can also use regular expressions. Ranges can also be used, for example salary >= 15000 AND salary <= 100000

Check (no pun intended) out this post also SQL Server does support regular expressions in check constraints, triggers are not always needed, it has examples about creating a case sensitive check constraint