In this tutorial, I am going to explain you about Check Constraint in SQL Server. We are already familiar with Primary Key Constraint and Foreign Key Constraint in SQL Server.

After Completing this tutorial you will be able to understand:

  • What is Check Constraint in SQL Server?
  • How to Create Check Constraint in SQL Server?

Introduction:

A Check Constraint is used to specify a condition on a row in a table. A Check Constraint must refer to only columns of that table. It cannot refer to columns of other tables. We can define a Check Constraint either using CREATE TABLE statement or be using ALTER TABLE Statement.

The syntax for defining a Check Constraint:

Where;

tablename specifies the name of the table.

column1, column2 specifies the name of the columns of the tablename.

check_constraintname specifies the name of Check Constraint.

column_name is the column on which you want to create your Check Constraint.

condition is the condition that must be met to succeed Check Constraint.

Example:

Suppose you have a condition that your table should accept Employee Records that have age between 18 to 40. Then in such situation, we can create a Check Constraint on Age column. Here is the example for such scenario:

You can see in the following CREATE TABLE Statement we have a created a Constraint called ageConstraint on Age column that will accept the value between 18 and 40. Let’s this Check Constraint with a Record Insertion. Run the following query.

If you run above query you will get the following error.

Check Constraint

Example 2:

Let’s create another example for Constraint. This time I want to create a Check Constraint for Gender Column. The value for Gender column should be Male or Female.

 

Try to insert the record with Gender value other than Male and Female.

 

You will get the following error after running above query.

Check Constraint

 

Create Check Constraint After Table Creation:

You can also create Check Constraint using ALTER TABLE Statement.

The syntax for creating Check Constraint using ALTER TABLE Statement:

Where;

tablename specifies the name of the table.

check_constraintname specifies the name of Check Constraint.

columnname is the column on which you want to create your Check Constraint.

condition is the condition that must be met to succeed Check Constraint.

Example:

How to drop a Check Constraint:

The Syntax for dropping a Check Constraint is:

Where;

tablename specifies the name of the table.

constraintname specifies the name of Check Constraint.

Example:

How to disable Check Constraint:

Syntax for disabling Check Constraint in SQL Server.

Where;

tablename specifies the name of the table.

constraintname specifies the name of Check Constraint.

Example:

How to enable Check Constraint:

The syntax for disabling Check Constraint in SQL Server.

Where;

tablename specifies the name of the table.

constraintname specifies the name of Check Constraint.

Example:

Conclusion:

Hope you understand how to create your own rule for the column in  SQL Server. I will appreciate, feedback and Comments.

View More:

Thank You.

Leave a Comment