Home » How to use CHECK in SQL

How to use CHECK in SQL

by Online Tutorials Library

How to use CHECK in SQL

In this article, you will learn how to use the CHECK keyword to the column in SQL queries.

What is CHECK in SQL?

CHECK is a SQL constraint that allows database users to enter only those values which fulfill the specified condition. If any column is defined as a CHECK constraint, then that column holds only TRUE values.

The following syntax adds the CHECK constraint to the column at the time of table creation:

We can easily use the CHECK constraint to one or more columns in one SQL table.

The following syntax adds the CHECK constraint to the column when the table already exists:

If you want to use the CHECK constraint at the time of table creation, you have to follow the steps given below:

  1. Create the new database
  2. Create a new table with the CHECK constraint
  3. Insert the values
  4. View the records of Table

Step 1: Create the Simple new database

First, you have to make a new database in Structured Query Language. The following query creates the new Voting Database in the SQL server:

Step 2: Create the New table and add CHECK

The following query creates the People_Info table in the Voting Database and adds the CHECK constraint to the People_Age column of the table:

Step 3: Insert the Values

The following INSERT queries insert the records of eligible people in the People_Info table according to the CHECK constraint applied on the People_Age column:

The following query shows the constraint failed error because we have applied the constraint on the People_Age column, which only holds a value of more than 18.

Step 4: View the Table’s Data

The following query shows the data of the People_Info table.

People_ID People_Name People_Gender People_Age People_Address
1001 Arush Male 20 Agra
1002 Bulbul Female 30 Lucknow
1004 Saurabh Male 20 Lucknow
1005 Shivani Female 25 Agra
1006 Avinash Male 22 Delhi
1007 Shyam Male 19 Banglore

As we can see in the above People_Info table, the People_Age column contains the age of those people having age more than 18.

Add Check Constraint on Multiple columns

The following CREATE TABLE query specifies the CHECK constraint on more than one column to the Doctor_Info table:

The following query inserts the multiple records of those doctors who are Cancer specialists, Male, and from the UK.

The following query shows the details of the Doctor_Info table:

Doctor_ID Doctor_Name Doctor_Disease Doctor_Gender Doctor_Country
1035 Jones Cancer Male U. K.
1015 Moris Cancer Male U. K.
1003 Harry Cancer Male U. K.
1044 Bunny Cancer Male U. K.
1025 Moria Cancer Male U. K.

Add CHECK constraint to Existing table

Any database user can easily add a CHECK constraint to the existing table by using the ADD keyword in the SQL ALTER query.

Syntax to specify CHECK constraint to the Existing table:

The following ALTER statementspecifies the CHECK constraint to the People_Address column of the above People_Info table:

Delete CHECK constraint from the table

The DROP keyword with ALTER statement allows the database users to remove the CHECK constraint from the column of the table.

If you want to remove the CHECK constraint from the SQL table, you can delete it by using the following syntax:

The following query deletes the CHECK constraint from the People_Address column of the People_Info table:


You may also like