Home » SQL » SQL CHECK Constraint

SQL CHECK Constraint

Overview: In this SQL tutorial, you will know and understand about SQL CHECK constraint and how to use t in SQL table.

What is SQL CHECK constraint?

SQL CHECK constraint help you to set condition on specify values for a column by using Boolean expression like greater then less ten or equal to. Sometime you need to set condition like No of Employee should not be -ve value or discount should not be in -ve.

Why you need CHECK constraint?

SQL CHECK constraint make you data more refine and accurate and help you do not deal with garbage data because you secure the unwanted values initially by condition using CHECK on columns.

Syntax: SQL CHECK Constraint

CREATE TABLE Table_name(
             column1 datatype1,
             column2 datatype2,
             column3 datatype3 CHECK(column3 > 0)
             );

so you can see CHECK keyword which specify the condition or Boolean expression on column3

Now lets understand it more with actual example by using Employee_info table, you need to use CREATE TABLE command to define CHECK constraint if you are creating new table.

Example #1: Apply CHECK constraint for one column

In below example we are applying CHECK constraint on Age column for employee, to just ensure that there should not be any value in -ve age. So we have put condition or Boolean expression on age should be greater then “0”

And this CHECK constraint help a lot during import data in table or insert value by using INSERT INTO command in SQL.

CREATE TABLE Employee_info(
             Employee_ID INT PRIMARY KEY,
             Employee_name VARCHAR(255) NOT NULL,
             Age INT CONSTRAINT Emp_age CHECK(Age > 0)
             );

So if you will insert -ve age via INSERT INTO command, the query will through the below error. (as you can see in the below image)

INSERT INTO Employee_info(Employee_ID, Employee_name, Age)
VALUES (1002, 'Robert', -23);

Error:

Error through if insert age value in negative number by using check constraint
Error through if insert age value in negative number

Example #1: Apply CHECK constraint for multiple columns

Applying CHECK constraint on multiple column by same way just to mention CHECK keyword for those column on which you want to apply CHECK constraint, as shown below code

CREATE TABLE Employee_info(
             Employee_ID INT PRIMARY KEY,
             Employee_name VARCHAR(255) NOT NULL,
             Age INT CONSTRAINT Emp_age CHECK(Age > 0)
             DOB year CHECK(DOB > 1900)
             Salary INT CHECK(Salary > 0)
             );

here we have applied CHECK on three different columns

How to disable CHECK constraints

If you want to disable a CHECK constraint for few column the you can use below Syntax:

ALTER TABLE Table_name
NOCHECK CONSTRAINT constraint_name;

Lets take below example to remove CHECK constrain on Age column for Employee_info table

ALTER TABLE Employee_info
NO CHECK CONSTRAINT Emp_age ;

It will disable the CHECK constraint and it will allow -ve value in age. this is just an example.

Conclusion:

CHECK Constraint help you to fix condition on column so that unwanted or garbage value should not be imported or inserted in the column of SQL table.