SQL Server Check Constraints restrict the values that are accepted by one or more columns
The constraint must be a predicate and the result of the predicate can be either True, False or Unknown, depending on the presence of NULLs.
If the predicate evaluates to UNKNOWN, then the constraint is not violated and the row can be inserted or updated in the table.
CREATE TABLE table_name ( col1 datatype1 [ NULL | NOT NULL ], col2 datatype2 [ NULL | NOT NULL ], ... CONSTRAINT constraint_name CHECK (col condition) );
Lets Look at an example of CHECK Constraint in SQL Server.
Creating a Check Constraint when Create a new table
Lets create a CHECK constraint on table column when creating a new table.
In following Create table statement, we create a CHECK constraint on Age column, which ensures that the value for Age column must be greater than 18.
CREATE TABLE dbo.Student ( StudId INT NOT NULL IDENTITY(1,1), NAME VARCHAR(100), Age INT CHECK (Age > 18), Grade VARCHAR(5) ) OR CREATE TABLE dbo.Student ( StudId INT NOT NULL IDENTITY(1,1), NAME VARCHAR(100), Age INT , Grade VARCHAR(5), CONSTRAINT Check_Student_Age CHECK(Age > 18) )
You can see, commands completed successfully. Lets verify, whether the CHECK constraint is created or not on table. You can use Sp_help Stored procedure
Lets Execute the Sp_help Stored Procedure.
EXEC Sp_help Student
You can see, CHECK Constraint is created on Age column and constraint name is Check_Student_Age.
You can also check whether the constraint is created or not using Object Explorer, as shown below.
Inside table, expand the Constraints folder there you can see CHECK constraint is created.
As you can see, it is just displyaing constraint name Check_Student_Age if you want to complete details then double click on this constraint name. You will see a new query editor window opens there you can see the table in design mode, and a check constraint dialog box which display constraints details.
Insert values into CHECK constraint Column
Lets see what happens when value for Age column is provided is greater than 18 during Insert in table student.
INSERT INTO dbo.Student (Name, Age, Grade) VALUES ('Rakesh Agarwal', 20, 'C')
You can see, records inserted successully.
Lets see record in Student table, and you can see record inserted in table successfully. Because value for Age column is greater than 18 which satisfied the CHECK condition that why records is inserted successfully.
SELECT * FROM dbo.Student
Lets try to insert record in student table for Age less than 18.
INSERT INTO dbo.Student (Name, Age, Grade) VALUES ('Mark Anthony', 17, 'B')
And you can see, this time CHECK constraint returns an error as value is less than18 which does not satisfy the CHECK condition (Age >18). Therefore It returns an error.
UPDATE values into CHECK constraint column
CHECK constraint also validate the values provided to column during updation.
In following statement we are trying to Update a value on Age column from 20 to 15, which is less than 18.
As you can see for StudId =1, Age value is 20. Now we will update Age value to 15.
SELECT * FROM dbo.Student
Lets update Age value for StudId =1 from 20 to 15.
Update student SET StudAge =10 where StudID=1
You can see, CHECK constraint also not allowing to update value for Age that is less than 18.
Providing NULL Value to CHECK Constraint column
In case if you provide a null value to the CHECK constraint column, it will not give any error and record will be inseterd successfully.
Because Null can be a unknown value or an absence of a value so so, CHECK constraint does not return error.
Lets see the table record, and you can see record is there.
SELECT * FROM dbo.Student
Drop CHECK Constraint
You can also drop an existing constraint using Alter table Statement for dropping constraint. You need to pass constraint name in this statement only when dropping a constraint, column name does not require.
If you do not know constraint name then you can use sp_help command (as used above) to get the constraint name.
Following statement drops an existing CHECK constraint from table column Age. You can see we have passed CHECK Constraint name only not column name.
ALTER TABLE dbo.Student DROP CONSTRAINT Check_Student_Age
You can see, commands completed successfully, Lets make sure whether the CHECK constriant is removed from column or not.
Lets quicky go to object explorer, First refresh table folder then, check constraints folder inside table Student.
You can see there is no constraints available on table now.
Adding a CHECK Constraint to column in existing table
You can also add CHECK constraint on existing table column.
As you have just removed the constraint from Age column, now we will add CHECK constraint for validating Age value for condition (Age >18) again by using Alter table Statement for adding constraint on table as given below.
Following Statement, adds CHECK constraint on table column Age for condition check Age >18
ALTER TABLE dbo.Student ADD CONSTRAINT Check_Student_Age CHECK(Age > 18)
As you can see, commands completed successfully, Lets quicky verify same in object explorer.