How to use CHECK Constraint in SQL

When it comes to database management, having flow control ensures better data quality and accuracy. One way to ensure these requirements is through the implementation of CHECK Constraints. A CHECK Constraint is a rule that a subset of data in a table must follow. It is an effective way to enforce business rules on a table column or a group of table columns, ensuring that the data inserted is correct.

In this article, we’ll take a closer look at CHECK Constraints and how to use them in SQL. We’ll also cover some important aspects, including syntax, examples, and considerations when implementing these constraints.

The Importance of CHECK Constraint

As a developer, you might have already found yourself in a situation where you’ve compromised data quality and accuracy due to incorrect data insertion. Using CHECK Constraints can greatly reduce such accidents and ensure data integrity and accuracy.

For example, suppose you have a database that stores a student’s data in a table. One of the columns in this table requires a valid email address. By implementing a CHECK Constraint, you make sure that any inserted data in the “email” column is formatted correctly, with the right characters and symbols needed for an email address.

Syntax of CHECK Constraint

The following is the syntax for the CHECK constraint in SQL.

CREATE TABLE table_name (
column_name1 datatype CONSTRAINT constraint_name CHECK (conditions),
column_name2 datatype,
….
);

• table_name: The name of the table
• column_name1: The name of the column you want to apply the check constraint to
• datatype: the attribute data type
• constraint_name: A user-specific name to identify this constraint (optional)
• conditions: the logic rule under the parentheses

How to Implement the CHECK Constraint

To implement a CHECK Constraint, follow the steps below:

1. First, open the SQL server management studio.
2. Select the database name and click on New Query.
3. Enter the CREATE command to create a table.
4. Add the table and all the table attributes with their respective data types.
5. Specify the CHECK constraint, including the condition within parentheses.
6. Run the query, and the table will be created.

Here is a real-life example of how to use CHECK Constraint in SQL:

CREATE TABLE Student_Data (
StudentID Int IDENTITY(1,1) NOT NULL,
UserID Int NOT NULL CONSTRAINT uc_UserID UNIQUE,
Email varchar(100) NOT NULL CONSTRAINT chk_Email CHECK(Email LIKE ‘%_@_%._%’),
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
);

In this example, we created a CHECK Constraint for the email address, ensuring it follows the email format.

Important Considerations

When implementing a CHECK Constraint in your database, consider the following:

• CHECK Constraint ensures data integrity, but it doesn’t prevent data from being inserted when the constraint is violated. Instead, an error message or warning is displayed.
• The CONSTRAINT keyword is optional; however, adding it for ease of identification is good practice.
• Ensure the constraint does not constrain too tightly; otherwise, it might create unnecessary errors when working with data.
• CHECK Constraints can be added for existing tables by using the ALTER TABLE command.

Examples of CHECK Constraints

Here are some more examples to illustrate how CHECK Constraints can be implemented in SQL:

Example 1: Checking for a Valid Phone Number

CREATE TABLE Customer (
CustomerID Int IDENTITY(1,1) NOT NULL,
Phone_Number varchar(20) NOT NULL CONSTRAINT chk_PhoneNumber CHECK(PHONE_NUMBER LIKE '[+]44[1-9][0-9]{8}' OR PHONE_NUMBER LIKE '[+]44[1-9][0-9]{9}' ),
Name varchar(50) NOT NULL,
Address varchar(100) NOT NULL,
);

In this example, we created a CHECK Constraint and enforced a UK phone format with “+44” with 10 or 11 digits.

Example 2: Ensuring Age Minimum Age

CREATE TABLE Employee_Data (
EmployeeID Int IDENTITY(1,1) NOT NULL,
Name varchar(50) NOT NULL,
Age int NOT NULL CONSTRAINT chk_Age CHECK(Age >= 18),
Address varchar(100) NOT NULL,
Salary int NOT NULL,
);

In this example, we created a CHECK Constraint that enforces the minimum age of 18 for newly inserted data.

Frequently Asked Questions

Conclusion

The CHECK Constraint is a tool that can help you improve your database’s data quality and minimize the likelihood of erroneous data. By implementing this constraint in your SQL code, you can ensure the integrity and accuracy of data to execute your queries efficiently. This article explains how to implement CHECK Constraints in SQL, along with some examples and important considerations. We hope this article helps you apply this valuable tool in your database management.

Useful links:

MySQL Official Website

Microsoft Docs: Create Check Constraints

📕 Related articles about MySQL

• What are the common MySQL Queries
• How to Use Alter Table – Add Statement in SQL
• MySQL Connecting to Server: A Comprehensive Guide
• How to use Aliases in SQL
• MySQL Text Data: A Comprehensive Guide
• How to use SELECT Query in SQL