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.