How to Create Alter And Drop Check Constraint In SQL Server with Examples

Neerajprasadsharma
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 465 red flag

Learn what is check constraint and how to use it in SQL Server

Introduction

CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range. The logical expression would be the following: salary >= 15000 AND salary <= 100000.

You can apply multiple CHECK constraints to a single column. You can also apply a single CHECK constraint to multiple columns by creating it at the table level. For example, a multiple-column CHECK constraint can be used to confirm that any row with a country/region column value of USA also has a two-character value in the state column. This allows for multiple conditions to be checked in one location.
CHECK constraint controls the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table(Primary key`s table), and CHECK constraints determine  the valid values from a logical expression.
Theory ref :- http://msdn.microsoft.com/en-us/library/ms188258(v=sql.105).aspx

Create Table With Check Constraint Syntax:

 Create Table YourTable_Name (yourColumn_Name1 DataType Constraint Constraint_Name CHECK (Check_Condition) , yourColumn_Name2 DataType)
Let us use this syntax and create a table.

Create Table TableWithCheck( Name Varchar(20) ,SchoolAdmissionAge Int Constraint CHK_AdmissionAgeCHECK(SchoolAdmissionAge>5 ) , CollageAdmissionAge Int )


There is a table, and you want to convert its column(s) with check constraint here is the solution with Alter table below.

Alter Table Add Check Constraint Syntax: Alter Table Table_Name Add Constraint Constraint_Name Check( Condition ) 
We have a table TableWithCheck, let us use the above Alter table syntax,  and Alter its another column CollageAdmissionAge in that way so it will only allow age not less than sixteen.

Alter Table TableWithCheck add Constraint Chk_collageaddAge check( CollageAdmissionAge>16)


In the example above , we have created constraint on the INT datatype column, we can create a check constraint on any datatype. Let us create it with varchar datatype.

    
Create Table StudentFinalResult (StudentName Varchar(25), Result Char(4) Constraint Chk_Result check (Result='Pass' or Result='Fail'))
  

Like any other constraint in SQL SERVER we can drop this constraint as well let us drop the CHK_Result Constraint of the Studentresult table.
Alter Table StudentFinalResult Drop Constraint Chk_Result





Test What We Learnt so far

Check Constraint limit the value in SQL Server, so let us test it to see what happens when we try to break the check constraint.
We have a constraint named Chk_Result on TableWithCheck table which won`t allow less than 16 in the CollageAdmissionAge collum. So let us  insert less than 16 in the CollageAdmissionAge column and see what happen.

Insert Into TableWithCheck values ('Neeraj' , 5 , 15)
Error message:
The INSERT statement conflicted with the CHECK constraint "CHK_AdmissionAge". The conflict occurred in database "tutorialsqlserver", table "dbo.TableWithCheck", column 'SchoolAdmissionAge'.
The statement has been terminated.


The error message shows the some information like in which database you are in, which table it is, check constraint name as well, but doesn`t show the constraint logic that is strange.




Page copy protected against web site content infringement by Copyscape

About the Author

Neerajprasadsharma
Full Name: NEERAJ Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here: https://www.sqlshack.com/author/neeraj/ https://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=12524731 https://www.mssqltips.com/sqlserverauthor/243/neeraj-prasad-sharma-/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)