![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL - Check Constraint
Check constraint is useful for the validity of the data entered into the particular table columns. The check constraint is the way of providing another protecting layer of the data.
If we specify check constraint on a column, then it will allow only certain values to be inserted in this column.
A check constraint is only applied to the single column, but there is the possibility of multiple check constraints in the single table.
Adding Check constraint on a Column
To apply a check constraint on a column level, we have to specify the check constraint just after the column name during table creation.
Syntax
Following is the syntax to specify the check constraint on column.
CREATE TABLE table_name ( column1 datatype(size), column datatype(size) constraint constraintName CHECK Check(columnName condition value), …, column datatype (size));
Example
In the following SQL query, we are creating a table named employees and specifying a column-level check constraint on one columns.
CREATE TABLE employees( EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE);
In the above query, we have specified the check constraint on the age column. According to this constraint, the age column will allow only those records to be inserted where the age is greater than 20.
Verification
To verify the created table, we can use the following query in the SQL −
EXEC sp_help "dbo.employees";
It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table.
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+ | constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication |constraint_keys | +------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+ | CHECK on column AGE | CK__employees__AGE__4CC05EF3 | (n/a) | (n/a) | Enabled | Is_For_Replication |([AGE]>=(20)) | | UNIQUE (non-clustered) | UQ__employee__57B58178E24D3C4F | (n/a) | (n/a) | (n/a) | (n/a) | C_Phone | +------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
Check constraint on multiple Columns
We can also add check constraint on multiple columns of a table In the following example, we are creating a table named students and specifying a column-level check constraint on multiple columns (AGE and FEE).
CREATE TABLE students( SID INT NOT NULL, NAME VARCHAR(20), AGE INT NOT NULL CHECK(AGE<=24), CITY VARCHAR(30), FEE NUMERIC NOT NULL CHECK(FEE>=15000));
According to both constraints created above, the age column will allow only those records where the age is less than 24, and the fee column will allow only those records where the fee is greater than 15,000.
Verification
To verify the created table, we can use the following query in the SQL −
EXEC sp_help "dbo.employees";
It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table.
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+ | constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication |constraint_keys | +------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+ | CHECK on column AGE | CK__employees__AGE__4CC05EF3 | (n/a) | (n/a) | Enabled | Is_For_Replication |([AGE]<=(24)) | | CHECK on column FEE | CK__students__FEE__5090EFD7 | (n/a) | (n/a) | Enabled | Is_For_Replication |([FEE]>=(15000))| +------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
Now, we're trying to insert values in the students table that are older than 24 and have a fee of less than 15,000.
INSERT INTO students VALUES(001, 'Aman Kumar', 25, 'Ranchi', 14000);
The query throws an error when we try to insert the data because we are utilizing the age, whose value is greater than 24.
Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "CK__students__AGE__4F9CCB9E". The conflict occurred in database "master", table "dbo.students", column 'AGE'.
Adding check constraint on the table Level
We must use the check constraint before completing the table creation in order to ensure the check constraint on the table level.
Syntax
Following is the syntax to specify the check constraint on the table level
CREATE TABLE table_name( column1 datatype(size), column2 datatype(size), … columnN datatype, CONSTRAINT constraint_name CHECK(column_name condition value));
Example
We are creating a table and specifying a table level check constraint on one of the columns in the following SQL query.
CREATE TABLE products( PID INT NOT NULL, PNAME VARCHAR(30), DELIVERY_CITY VARCHAR(20), DATE_OF_ORDER Date NOT NULL, PRICE INT, PRIMARY KEY(PID), CONSTRAINT Constraint_DOO CHECK(DATE_OF_ORDER <= '2023-02-09'));
In the above SQL query, we have specified a check constraint on the DATE_OF_ORDER column according to the check constraint only those records where DATE_OF_ORDER is less than "2023-02-09" are alklowed in this column.
Verification
To verify the created table, we can use the following query in the SQL −
EXEC sp_help "dbo.products";
It will show all the details of the created table, including how many columns have check constraints on the table level.
+-------------------------------+-----------------+---------------+---------------+----------------+------------------------+-------------------------------+ | constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication |constraint_keys | +-------------------------------+-----------------+---------------+---------------+----------------+------------------------+-------------------------------+ | CHECK on column DATE_OF_ORDER | Constraint_DOO | (n/a) | (n/a) | Enabled | Is_For_Replication |([DATE_OF_ORDER]<='2023-02-09')| | PRIMARY KEY (clustered) | PK__products | (n/a) | (n/a) | (n/a) | (n/a) | PID | +-------------------------------+-----------------+---------------+---------------+----------------+------------------------+-------------------------------+
Now, we're writing a query to insert values in the students table that have a Date_of_order is greater than ('2023-02-09').
INSERT INTO products VALUES(001, 'Nike Shoe', 'Ranchi', '2023-02-11', 2000);
The query throws an error when we try to insert the data because we are utilizing the date, whose value is greater than '2023-02-09'.
Check constraint after table creation
Suppose there arises a scenario where we need to apply the check constraint after creating the table; or on an already existing table in a database, SQL provides a way to do that. In this situation, we can use the ALTER statement to apply the check constraint to a column of a table that has already been created.
Syntax
ALTER TABLE table_name ADD CONSTRAINT ConstraintName CHECK(ColumnName condition Value);
Example
Consider that we have a customers table with an age column. To add a check constraint to the age column, we are using the query below.
ALTER TABLE customers ADD CONSTRAINT Constraint_Age CHECK (AGE >= 21);
Verification
Use this SQL query to check the newly formed or changed table −
EXEC sp_help 'dbo.customers';
It will display all of the table's information, including the constraint we added to the age column.
+---------------------+-----------------+---------------+---------------+----------------+------------------------+----------------+ | constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication |constraint_keys | +---------------------+-----------------+---------------+---------------+----------------+------------------------+----------------+ | CHECK on column AGE | Constraint_Age | (n/a) | (n/a) | Enabled | Is_For_Replication |([AGE]>=(21))) | +---------------------+-----------------+---------------+---------------+----------------+------------------------+----------------+
Removing a check constraint
If there is a way to apply a constraint on a column, then you must also be able to delete the constraint from that column. To do that, you can use the ALTER… DROP statement.
Syntax
Following is the syntax to remove a constraint from the table −
ALTER TABLE table_name DROP CONSTRAINT constraint_set;
Example
Following example demonstrates how to drop the check constraint from the Customers table created above. We first check if any column in the Customers table has the check constraint and if it does, we can use the ALTER command to remove it.
ALTER TABLE customers drop CONSTRAINT Constraint_Age;
Verification
Here we can see that earlier we added a check constraint on the age column. However, after using the above SQL query, there is no constraint in the table, as shown in the table below −
+-------------------------+---------------------------------+---------------+----------------+-----------------+ | constraint_type | constraint_name | update_action | status_enabled | constraint_keys | +-------------------------+---------------------------------+---------------+----------------+-----------------+ | PRIMARY KEY (clustered) | PK__CUSTOMER__3214EC270E48CD10 | (n/a) | (n/a) | ID | +-------------------------+---------------------------------+---------------+----------------+-----------------+