What is Check Constraint in SQL?

If you are working with SQL databases, then you are definitely familiar with the concept of constraints. Constraints are rules that restrict the data that can be entered into a database table. They are used to ensure data accuracy, consistency, and integrity within the database. There is one type of constraint that is commonly used in SQL is the check constraint. In this article post, you will learn about check constraints in SQL, how they work, and how to use them effectively in your SQL database. So, let’s get started!

Introduction to Check Constraint in SQL

It is very important to ensure that the data being entered into the database is accurate, consistent, and meets certain criteria while designing a database in any DBMS. In this way, constraints come into play. Constraints are used to restrict the data that can be entered into the database table. They are used to ensure that the data meets certain criteria, such as a minimum or maximum value, or a specific format of data. Check constraint in SQL is one type of constraint that is commonly used in SQL.

What is a Check Constraint in SQL?

A check constraint is a type of constraint that is used to restrict the values that can be entered into a database column. It specifies a condition that must be satisfied before a value can be inserted or updated in a column. It can be used to ensure that the values entered into a column are within a certain range, or that they meet a certain condition. Check constraints are used to enforce business rules and data integrity.

How does Check Constraint work in SQL?

Check Constraint works by limiting the type of data that can be entered into a column or a group of columns in a table. When you create a Check Constraint, you specify the condition that must be met for the data to be entered into the columns. If the data does not meet the condition, the Check Constraint will prevent it from being entered into the columns, an error is thrown, and the transaction is rolled back.

When a check constraint is defined, it is evaluated whenever data is inserted or updated in the table. This ensures that only valid data is stored in the database, maintaining data integrity. For example, if you create a Check Constraint on a column called “Age” in a table called “CHK_Employees” that specifies that the age must be between 18 and 30, any data entered into the column that does not meet this condition will be rejected.

Benefits of Using Check Constraints

Refer below to the benefits of using check constraints –

1. Ensures data accuracy and consistency
2. Prevents data corruption and errors
3. Improves data quality
4. Reduces the need for data cleansing and correction
5. Saves time and effort in data management

Limitations of Check Constraints

Refer to below limitations of check constraints –

1. Check constraints are designed to handle simple validation rules, such as checking for a positive value in a column. More complex validation rules may require the use of triggers or other mechanisms.
2. Check constraints can add overhead to your database by slowing down insert and update operations. This can be a concern in high-traffic databases or those with a large number of check constraints.

Advantages of using check constraints

Refer below to the advantages of using check constraints in SQL –

1. Check constraints to ensure that the data being entered into your database meets certain criteria, which ensures data accuracy and consistency. This can help to prevent data entry errors and improve the overall quality of your data.
2. Check constraints are used to enforce business rules, which ensures that the data being entered into your database is valid and meets your business requirements.
3. Improves data integrity: Check constraints improve data integrity by preventing invalid data from being entered into your database.
4. Check constraints can also help to prevent malicious data from being inserted into your database. By restricting the range of values that can be entered into a column, you can ensure that only valid data is stored in your database.
5. Check constraints can help to improve the performance of your database by reducing the amount of invalid data that needs to be processed. This can lead to faster query execution times and better overall performance.

Disadvantages of using check constraints

Refer below to the disadvantage of using check constraints in SQL –

1. Check constraints can be complex to write, especially if you’re dealing with complex business rules.
2. Check constraints can slow down database performance if they are used excessively or if they are too complex.
3. Check constraints can make it difficult to modify your database schema, especially if you have a large number of them.

Best Practices for Using Check Constraints

Refer below best practices for using check constraints in SQL –

1. Use Check Constraint to enforce data integrity rules and improve data quality.
2. Check constraints should be as simple as possible to ensure that they are easy to maintain and understand. Complex constraints can be difficult to debug and may lead to performance issues.
3. When creating check constraints, it is important to use meaningful names that describe the purpose of the constraint. This can make it easier to understand and maintain your database over time.
4. Before deploying your database, it is important to test your check constraints thoroughly to ensure that they are working as expected. This can help to prevent data quality issues and improve the overall performance of your database.

Common Mistakes with Check Constraints

Refer to below the common mistakes with check constraints in SQL –

1. Complex constraints can be difficult to understand and maintain and can lead to performance issues. It is important to keep your constraints as simple as possible.
2. It is essential to test your check constraints thoroughly before deploying your database. Failing to do so can lead to data quality issues and performance problems.
3. Using generic or unclear names for your check constraints can make it difficult to understand and maintain your database over time.

Applications of Check Constraint in SQL

You will learn here the application of check constraint in SQL as follows –

1. Check Constraint Creation on Multiple Columns Without Defining Its Name While Table Creation

In this example, we will create a table named ‘CHK_Employees1‘ and assign a check constraint without defining its name with the ‘DateOfJoining‘ column and ‘Age‘ column, where the check condition is for ‘DateOfJoining‘, will be less or equal to the current date and check condition is for ‘Age‘ will be less or equal to 18 Years. In this scenario, DBMS will create a check constraint and also define a system-generated default name for this check constraint which we will see ahead. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column1 Data_Type,
Column2 Data_Type,
Column3 Data_Type,
Column4 Data_Type CHECK (Column_Name Condition));

Example:

CREATE TABLE CHK_Employees1 (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfJoining DATE CHECK(DateOfJoining <=GETDATE()), Age INT CHECK (Age>=18));

Screenshot: Below screenshot for reference –

You can see in the above query example we have not provided any specific name for these check constraints. In this scenario, DBMS has provided a system-generated default name for these check constraints (CK__CHK_Emplo__DateO__74794A92 and CK__CHK_Employe__Age__756D6ECB), which can be seen in the above screenshot marked with yellow color on the left side.

2. Check Constraint Creation on a Single Column With Defining Its Name While Table Creation

In this example, we will create a table named ‘CHK_Employees2‘ and assign a check constraint named ‘CHK_Employee2_DOJ‘ with the ‘DateOfJoining‘ column. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column1 Data_Type,
Column2 Data_Type,
Column3 Data_Type,
Column4 Data_Type,
CONSTRAINT Constraint_Name CHECK (Column_Name Condition));

Example:

CREATE TABLE CHK_Employees2 (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfJoining DATE,
Age INT,
CONSTRAINT CHK_Employee2_DOJ CHECK (DateOfJoining <=GETDATE()));

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the check constraints name ‘CHK_Employee2_DOJ‘, which can be seen in the above screenshot marked with yellow color on the left side.

3. Check Constraint Creation on Multiple Columns With Defining Its Name While Table Creation

In this example, we will create a table named ‘CHK_Employees3‘ and assign a check constraint named ‘CHK_Employee3_DOJ_Age‘ with the ‘DateOfJoining‘ and ‘Age‘ columns. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column1 Data_Type,
Column2 Data_Type,
Column3 Data_Type,
Column4 Data_Type,
CONSTRAINT Constraint_Name CHECK (Column_Name Condition AND Column_Name Condition));

Example:

CREATE TABLE CHK_Employees3 (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfJoining DATE,
Age INT,
CONSTRAINT CHK_Employee3_DOJ_Age CHECK (DateOfJoining <=GETDATE() AND Age>=18));

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the check constraints name ‘CHK_Employee3_DOJ_Age‘, which can be seen in the above screenshot marked with yellow color on the left side.

4. Check Constraint Creation on Multiple Columns Without Defining Its Name After Table Creation

In this example, We will create check constraints without providing its name with the ‘DateOfJoining‘ column and ‘Age‘ column of an existing table named ‘CHK_Employees4‘. In this scenario, DBMS will create a check constraint and also define a system-generated default name for this check constraint which we will see ahead. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ADD CHECK (Column_Name Condition AND Column_Name Condition);

Example:

ALTER TABLE CHK_Employees4
ADD CHECK (DateOfJoining <=GETDATE() AND Age>=18);

Screenshot: Below screenshot for reference –

You can see in the above query example we have not provided any specific name for these check constraints. In this scenario, DBMS has provided a system-generated default name for these check constraints (CK__CHK_Employees4__7D0E9093), which can be seen in the above screenshot marked with yellow color on the left side.

5. Check Constraint Creation on a Single Column With Defining Its Name After Table Creation

In this example, We will create a check constraint by providing its name ‘CHK_Employee5_DOJ‘ with the ‘DateOfJoining‘ column of an existing table named ‘CHK_Employees5‘. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name CHECK (Column_Name Condition);

Example:

ALTER TABLE CHK_Employees5
ADD CONSTRAINT CHK_Employee5_DOJ CHECK (DateOfJoining <=GETDATE());

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the check constraints name ‘CHK_Employee5_DOJ‘ for this check constraint, which can be seen in the above screenshot marked with yellow color on the left side.

6. Check Constraint Creation on Multiple Columns By Defining Its Name After Table Creation

In this example, We will create a check constraint by providing its name ‘CHK_Employee6_DOJ_Age‘ with the ‘DateOfJoining‘ and ‘Age‘ columns of an existing table named ‘CHK_Employees6‘. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name CHECK (Column_Name Condition AND Column_Name Condition);

Example:

ALTER TABLE CHK_Employees6
ADD CONSTRAINT CHK_Employee6_DOJ_Age CHECK (DateOfJoining <=GETDATE() AND Age>=18);

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the check constraints name ‘CHK_Employee6_DOJ_Age‘ for this check constraint, which can be seen in the above screenshot marked with yellow color on the left side.

7. Delete or Drop a Check Constraint

You will learn here to delete or drop an existing check constraint. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
DROP CONSTRAINT Constraint_Name;

Example:

ALTER TABLE CHK_Employees1
DROP CONSTRAINT CK__CHK_Emplo__DateO__74794A92;

Screenshot: Below screenshot for reference –

You can see in the above query example we have deleted the check constraint ‘CK__CHK_Emplo__DateO__74794A92‘, which can not be seen in the above screenshot marked with yellow color on the left side.

8. To Check List of all Check Constraints in SQL Server

You will learn here to do checklists of present check constraints in your SQL Server database. Refer to the below syntaxes –

Example:

SELECT
name AS Constraint_Name,
is_disabled AS Constraint_Disable_Status,
is_not_trusted AS Constraint_Trusted_Status,
definition AS DEFINITION,
OBJECT_NAME(parent_object_id) AS Table_Name
FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id)= 'CHK_Employees1';

Screenshot: Below screenshot for reference –

You can see in the above query example all the check constraint lists are showing with their disabled status.

9. How to Disable a Check Constraints in SQL Server

You will learn here, how to disable an existing check constraint in your SQL Server database. Refer to the below syntaxes –

Syntax:

ALTER TABLE table_name
NOCHECK CONSTRAINT constraint_name;

Example:

ALTER TABLE CHK_Employees1
NOCHECK CONSTRAINT CK__CHK_Employe__Age__756D6ECB;

Screenshot: Below screenshot for reference –

You can see in the above query example that the check constraint is disabled.

10. How to Enable Check Constraints in SQL Server

You will learn here, how to enable an existing check constraint in your SQL Server database. Refer to the below syntaxes –

Syntax:

ALTER TABLE table_name
WITH CHECK CHECK CONSTRAINT constraint_name;

Example:

ALTER TABLE CHK_Employees1
WITH CHECK CHECK CONSTRAINT CK__CHK_Employe__Age__756D6ECB;

Screenshot: Below screenshot for reference –

You can see in the above query example that the check constraint is enabled.

Test Scenario of Check Constraint in SQL Server

You will learn here all test scenarios of check constraint that is it working or not using INSERT – SELECT statements. If you remembered that we have created ‘CHK_Employees1‘ and implemented a check constraint named ‘CK__CHK_Employe__Age__756D6ECB‘ which doesn’t allow ages less than 18 years to insert, now we will take this table for testing with insert statement whether check constraint ‘CK__CHK_Employe__Age__756D6ECB‘ is working properly or not. We will try to insert the value as 17 in the Age column, which should not allow us to insert and throw an error. Let’s see as follows –

Example:

INSERT INTO CHK_Employees1 (EmployeeID,FirstName,LastName,DateOfJoining,Age)
VALUES('12','SQLPOST','Academy','2020-01-01','17');

Screenshot: Below screenshot for reference –

You can see in the above screenshot that we have provided 17 values for the column named ‘Age‘ which is less than 18 and hence DBMS terminated the statement and threw an error ‘The INSERT statement conflicted with the CHECK constraint “CK__CHK_Employe__Age__756D6ECB”. The conflict occurred in database “SQLPOST”, table “dbo.CHK_Employees1”, column ‘Age’.’, Now we will try the same statement with the right value meaning 18 years, and let’s see.

Example:

INSERT INTO CHK_Employees1 (EmployeeID,FirstName,LastName,DateOfJoining,Age)
VALUES('12','SQLPOST','Academy','2020-01-01','18');

SELECT * FROM CHK_Employees1;

Screenshot: Below screenshot for reference –

You can see in the above screenshot that we have provided desired value as 18 for the column named ‘Age‘ which satisfied the condition defined in the check constraint hence insert statement was executed and the record is inserted successfully into the table named ‘CHK_Employees1‘

Note: We have tried to explain all possible scenarios about the check constraint at a summary level, where you have to read the complete article and try to elaborate on all possible scenarios with you and give more time to practice it.

FAQs

Conclusion

Check constraints are an important rule for ensuring data accuracy, consistency, and integrity in your SQL database. They can be used to enforce business rules and ensure that the data being entered into your database tables meets certain criteria. While check constraints have some disadvantages, they provide many benefits that make them valuable in any SQL database. We hope you would have liked this very well.

Please share this on your social media network so that it can be available to even more needy persons, which may benefit them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.

Recommended Article Posts

DDL Full Form and How is it used in SQL?	DML Full Form and How is it used in SQL?
DCL Full Form and How is it used in SQL?	TCL Full Form and How is it used in SQL?
What is DQL in SQL?	What are Integrity Constraints in DBMS?
Key Constraints in DBMS With Fundamentals	Primary Key Constraint in SQL
Unique Key in DBMS and Best Practices	Foreign Key in DBMS and Best Practices
What is Default Constraint in SQL?	What is Database Language?