CHECK Constraint

What is a CHECK Constraint in MySQL?

A CHECK Constraint in MySQL sets a rule for a column. The value entered in that column must follow the rule. It allows only valid data to be stored in the table.

Syntax:

CREATE TABLE table_name (
    column_name data_type CHECK (condition)
);

Example:

let the Student table contains three columns: Student_ID, Name, and Age, with three rows.

The Age field is set with a CHECK Constraint because every age value must satisfy the condition Age >= 18.

If you enter Age = 20 or Age = 18, MySQL accepts these values because they satisfy the defined condition.

If you enter Age = 16, MySQL does not allow the value because it does not satisfy the CHECK condition.

Output:

+------------+---------+-----+
| Student_ID | Name    | Age |
+------------+---------+-----+
| 101        | Rahul   | 20  |
| 102        | Amit    | 21  |
| 103        | Neha    | 19  |
+------------+---------+-----+

Explanation:

The Age column has a CHECK Constraint. The value stored in this column must satisfy the defined condition.

Because a CHECK Constraint is set on this column, MySQL does not permit values that do not satisfy the specified condition.

A CHECK Constraint is used to validate data according to the defined condition. It does not create a unique identity for a record.

How Do You Set a CHECK Constraint on a Table in MySQL?

You can set a CHECK Constraint on a table by using the following two methods:

1. While creating a new table using the CREATE TABLE statement.

   
   

   Example:

   CREATE TABLE Student (
       Student_ID INT,
       Name VARCHAR(50),
       Age INT CHECK (Age >= 18)
   );
   

   
   

   Steps:

   1. First, you write the CREATE TABLE statement, and then you write the table name.
   2. Open the parentheses (.
   3. Inside the parentheses, write the column names and their data types.
   4. For the column that you want to apply the CHECK Constraint, write the CHECK keyword with the condition after the data type.
   5. Write the remaining column names with their data types, separating each field with a comma.
   6. Close the parentheses ) and end the query with a semicolon ;.
   7. Finally, execute the query to create the table with the CHECK Constraint.

2. By adding a CHECK Constraint to an existing table using the ALTER TABLE statement.

   
   

   Example:

   ALTER TABLE Student
   ADD CHECK (Age >= 18);
   

   
   

   Steps:

   1. First, write the ALTER TABLE statement.
   2. Next, write the name of the existing table.
   3. Write the ADD CHECK clause.
   4. Inside the parentheses (), write the condition that you want to apply to the column.
   5. Close the query and end it with a semicolon ;.
   6. Finally, execute the query to add the CHECK Constraint to the existing table.

Advantages of a CHECK Constraint:

• Validates Data Before Storing

  When you set a CHECK Constraint on a column, MySQL checks the data before storing it in the database. If users insert or update records, it allows only valid values to be entered into the column.

• Prevents Invalid Values

  A CHECK Constraint checks whether the entered value matches the condition set on the column. If a user enters an invalid value, the CHECK Constraint does not allow it.

• Maintains Data Accuracy

  When you set a CHECK Constraint on a column, it applies rules to the column values. This helps keep the data accurate by preventing invalid values from being entered into the column.

• Improves Data Consistency

  A CHECK Constraint helps keep the data consistent by making sure the data follows the predefined rules throughout the table.

Common Mistakes When Setting a CHECK Constraint in MySQL

When you set a CHECK Constraint on a column, you should avoid the following mistakes:

1. Writing an Incorrect Condition

   When you set a CHECK Constraint on a column and apply a condition on that column, the value is accepted when it matches the condition. If the condition is wrong, it may permit invalid values or reject valid values.

   Example:

   If you set a CHECK condition Age >= 18, then entering Age = 15 is an invalid value and MySQL does not allow it.

2. Applying CHECK Constraint on the Wrong Column

   When you apply a CHECK Constraint on a column, you should be careful to check the data type because you should enter the data according to the matching data type.

   Example:

   If you apply a condition on the Age column, it should use a numeric data type like INT. You should not apply the same condition to a Name column with a VARCHAR data type.

3. Inserting Values That Do Not Match the Condition

   If you insert a value into a column where a CHECK Constraint is set, you should match the value with the CHECK condition before entering it. You can put the value only when it applies to the condition.

   Example:

   If a CHECK condition is set as Salary > 0, you cannot insert a negative value like Salary = -5000.

4. Forgetting the CHECK Condition

   You should not forget the CHECK condition if you enter values into that column. When you forget the specified rule defined on the column, you can enter the wrong value.

   Example:

   If the CHECK condition is Marks >= 40 and you forget this rule, you may try to enter a marks value less than 40.

5. Choosing the Incorrect Data Type

   You should choose the correct data type for the CHECK Constraint column because the condition should work properly with that data type.

   Example:

   A condition like Age >= 18 should be applied to an INT column because age stores numeric values.

6. Creating Unnecessary CHECK Constraints

   If you create unnecessary CHECK Constraints on one or more columns, it can make the database design more complex. For example, creating too many conditions on different columns can make it difficult to manage and update the database.

   Example:

   Creating many unnecessary conditions on columns like Age, Salary, and Marks can make the table difficult to manage.

What is a Composite CHECK Constraint and Why is it used?

A Composite CHECK Constraint is applied to two or more columns in a table. It ensures that the combined values of these columns satisfy the specified condition before storing a record in the table.

It is used when a condition depends on more than one column. In such cases, two or more columns are checked together using a CHECK Constraint to ensure that only valid combinations of values are stored in the table.

Syntax:

CREATE TABLE table_name
(
    column1 datatype,
    column2 datatype,
    column3 datatype,
    CHECK (condition)
);

Here,

➤ table_name → Specifies the name of the table.

➤ CHECK (condition) → Specifies the condition that the combined values of two or more columns must satisfy.

Example:

CREATE TABLE Student_Course
(
    Student_ID INT,
    Course_Name VARCHAR(50),
    Start_Date DATE,
    End_Date DATE,
    CHECK (End_Date > Start_Date)
);

Output:

After executing the above query, the Student_Course table is created successfully.

The following table shows valid records stored in the Student_Course table.

+------------+-------------+------------+------------+
| Student_ID | Course_Name | Start_Date | End_Date   |
+------------+-------------+------------+------------+
| 101        | Cloud       | 2026-01-15 | 2026-03-01 |
| 102        | PHP         | 2026-02-01 | 2026-04-01 |
| 103        | CSS         | 2026-03-01 | 2026-05-01 |
+------------+-------------+------------+------------+

Explanation:

In the above table, Start_Date and End_Date are checked together using the CHECK Constraint.
The condition End_Date > Start_Date makes sure that the end date is always after the start date.
Therefore, invalid combinations of Start_Date and End_Date cannot be added to the table.

Note:

• It does not identify records in a table and does not make values unique.
• It only ensures that the combined values of two or more columns follow the specified condition.