SQL Server CHECK Constraint

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.

SYNTAX

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.

Create a check constraint when creating 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 just displaying constraint name Check_Student_Age. If you want to see the 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 successfully.

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 the update.

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 inserted 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 constraint is removed from column or not.

Lets quickly 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 quickly verify same in object explorer.

Also Read..

SQL Primary Key

SQL Composite Key

SQL Foreign key

SQL Default Constraint

SQL Unique Key