Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
1.1.
CHECK Constraint in SQL
1.2.
EXAMPLES
2.
FAQs
3.
Key Takeaways
Last Updated: Mar 27, 2024

SQL - CHECK Constraint

Introduction

CHECK Constraint in SQL

CHECK constraint in SQL is used to check whether a value entered is valid or not according to the condition. We can say that the CHECK constraint in SQL is a type of integrity constraint that controls the value in a particular column. It ensures that the value inserted or updated in the column must be matched with given constraints. If the condition is evaluated as false, the record has not entered the table as the data violates the constraints.

EXAMPLES

Now, let's see some examples to understand CHECK constraint in SQL.

CHECK constraint in SQL with columns 

The following example shows how to use CHECK constraint in SQL with columns.

The following statement creates a new table called EMPLOYEE and adds columns.

CREATE TABLE EMPLOYEE(

   EmpID   INT  PRIMARY KEY      NOT NULL,

   Name VARCHAR (20)                 NOT NULL,

   Age  INT                                      NOT NULL CHECK (Age >= 18),

   Address  VARCHAR (25)

);

We have applied CHECK Constraint on Age, putting a condition to the values that can be inserted into the column of the table that only those values that are greater than or equal to 18, otherwise it violates the rule.

Now test this condition by inserting some values into the table

INSERT INTO EMPLOYEE( EmpID, Name, Age, Address) 

VALUES(1, ‘Aman’, 23, ‘Delhi’),

(2, ‘Neha’, 19, ‘Pune’);

Since the Age of Employee in both the entries is greater than or equal to 18. So these entries are valid and inserted into the table.

OUTPUT

The EMPLOYEE table will look like this:

EmpID

Name

Age

Address

1

Aman

23

Delhi

2

Neha

19

Pune

Now, if we try to run the following command:

INSERT INTO EMPLOYEE( EmpID, Name, Age, Address)

VALUES(3, ‘Vivek’, 17, ‘Noida’);

OUTPUT

This will show the error as the Age is not greater than or equal to 18 (as given in the constraints of the CHECK condition). 

SQL CHECK on ALTER TABLE.

Now we will see SQL CHECK on ALTER TABLE.

We create a new employee table with the following command, without any check condition.

CREATE TABLE EMPLOYEE(

   EmpID   INT  PRIMARY KEY      NOT NULL,

   Name VARCHAR (20)                 NOT NULL,

   Age  INT                                      NOT NULL,

   Address  VARCHAR (25)

);

Now the employee table is created, and we want to create a CHECK constraint on the Column 'Age'. We will run the following statement:

ALTER TABLE EMPLOYEE

ADD CHECK (Age>= 18);

We can also write this in the following manner, which supports naming the Check constraint in SQL.

ALTER TABLE EMPLOYEE

ADD CONSTRAINT Chk_Employee CHECK (Age>=18);

Again, we can test this condition by inserting some values into the table.

INSERT INTO EMPLOYEE( EmpID, Name, Age, Address) 

VALUES(1, ‘Aman’, 23, ‘Delhi’),

(2, ‘Neha’, 19, ‘Pune’);

Since the Age of Employee in both the entries is greater than or equal to 18. So these entries are valid and inserted into the table.

OUTPUT

The EMPLOYEE table will look like this:

EmpID

Name

Age

Address

1

Aman

23

Delhi

2

Neha

19

Pune

Now, if we try to run the following command:

INSERT INTO EMPLOYEE( EmpID, Name, Age, Address)

VALUES(3, ‘Vivek’, 17, ‘Noida’);

OUTPUT

This will show the error as the Age is not greater than or equal to 18 (as given in the constraints of the CHECK condition). The error will be like

ERROR 3819 (HY000): Check constraint ‘“constraint_name” is violated.

Dropping the CHECK constraints

To drop the CHECK constraint in SQL, we can use the following command:

ALTER TABLE EMPLOYEE 

DROP CONSTRAINT constraint_name;

Or 

ALTER TABLE EMPLOYEE 

DROP CHECK constraint_name;

Generally, we use DROP CHECK constraint_name in MySQL.

The above statement will drop that CHECK condition, and now there will be no rule to insert values according to that CHECK condition. Thus the above examples explains how we can use CHECK constraint in SQL.

Must Read SQL Clauses

FAQs

  1. What is SQL?
    SQL( Structured Query Language) lets you access and manipulate the databases.
    SQL can retrieve, create, update, insert and delete data from the database. SQL ( Structured Query Language) is the standard language for dealing with Relational Databases.
     
  2. What are some rules to keep in mind while using CHECK constraint in SQL?
    The following rules are to be followed:
    Subqueries or nested queries are not permitted when using the CHECK constraint in SQL.
    We can not refer to another column in another table using the CHECK constraint in SQL.
     
  3. What is a Subquery?
    A subquery is a query within another query. Subqueries are also known as inner queries or nested queries. They are the tool for performing the operations in multiple steps. Subqueries are embedded within the WHERE clause.

Key Takeaways

In this blog, we introduce the CHECK constraint in SQL. We learned how to use CHECK with columns in SQL and use ALTER TABLE, along with the naming of CHECK constraints in SQL. We also learned the dropping of the CHECK constraint in SQL.

Also Read - TCL Commands In SQL, wget command in linux

Click here to learn more about different topics related to database management systems. Visit here for the top 100 SQL problems asked in various product and service-based companies like Google, Microsoft, Infosys, IBM, etc.

Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation.

Live masterclass