When working with MySQL databases, it’s often necessary to ensure that certain rules or conditions are met when inserting or updating data. One way to achieve this is through the use of CHECK constraints, which allow you to specify a condition that must be true for any data insertion into a particular column. This can be especially useful for enforcing data integrity, preventing errors and inconsistencies, and ensuring that your database remains well-organized and efficient.

This write-up will provide a profound understanding of adding or removing the CHECK constraint in MySQL.

How to Add or Remove CHECK Constraint in MySQL?

The “CHECK” constraint in MySQL is utilized to specify the condition on a particular column that must be fulfilled while inserting or updating any data in that column. To add or remove the CHECK constraint in MySQL, the “ALTER TABLE” command can be utilized, which is used to modify the existing table data.

Let’s move to the syntax of adding or removing the CHECK constraint in MySQL.

The syntax to add the “CHECK” constraint in MySQL is given below:

Here in this syntax, “ADD CONSTRAINT” is used with the name “[const_name]” to add the CHECK constraint with a specific condition “[condition]“, to the table named “[tab_name]“.

Moreover, the “CHECK” constraint in MySQL can also be added while creating a table, to understand this concept follow the given below syntax:

In the above syntax, the CHECK constraint is created as “CHECK ([condition])” and in that “[condition]” represents a particular condition/criterion.

The syntax to remove the “CHECK” constraint in MySQL is given below:

In the above syntax, “DROP CONSTRAINT” is used with the name “[constraint_name]” to remove the CHECK constraint.

Let’s head toward the examples to see how to add or remove the CHECK constraint in MySQL.

How Do I Add the CHECK Constraint in MySQL?

To add the “CHECK” constraint in MySQL you must have an existing table or you can create a table by following the given below example:

The given query will create a new table named “lh_check”.

Output

The output clearly states that the table has been created without the CHECK constraint.

To add the CHECK constraint to the “lh_check” table, the “ALTER TABLE” can be utilized. An example of adding the CHECK constraint name “age_constraint” that ensures the “age” column must be equal to or greater than 18 is given below:

Output

The output depicts that the CHECK constraint name “age_constraint” has been added.

How to Confirm Whether a CHECK Constraint is Added or Not in MySQL?

To confirm whether a CHECK constraint is added or not, the “SHOW CREATE TABLE” command can be utilized in MySQL as follows:

In the above command, the CHECK constraint is confirmed in the “lh_check” table.

Output

The output “CONSTRAINT `age_constraint` CHECK ((`age` >= 18))” showed that the CHECK constraint has been added.

Insert Data Into the Table

Let’s insert data in the “lh_check” table that does not meet the condition of the “age_constraint” as given below in the command:

In the above command, the “age” value is “15” but according to the “age_constraint”, the value of the “age” column must be equal to or greater than “18”.

Output

The output “ERROR 3819 (HY000): Check constraint ‘age_constraint’ is violated.” showed that the value can’t be inserted if the value does not meet the condition of the constraint.

Now let’s insert data in the “lh_check” table that meets the condition of the “age_constraint” as given below in the command:

In the above command, the “age” value is “19” which is greater than “18”.

Output

The output showed that the value has been added, which means the value can only be inserted if it meets the condition of the check constraint.

How to Create/Define a CHECK Constraint in a Table’s Column?

In MySQL, the “CHECK” constraint can be created to specify particular criteria while creating a table. An example to create the “CHECK” constraint while creating the “lh_check” table is given below:

In the above example, the CHECK constraint is “CHECK (age >= 18)” which means the condition is applied to the “age” column.

Output

The output showed that the table has been created with CHECK constraint.

How Do I Remove a CHECK Constraint From a Column in MySQL?

The CHECK constraint in MySQL can be removed by using the DROP CONSTRAINT along with the “ALTER TABLE” statement.

An example to remove the CHECK constraint using the “DROP CONSTRAINT” command is given below:

Output

The output showed that the CHECK constraint name “age_constraint” has been removed by using the “DROP CONSTRAINT” command.

An example to remove the CHECK constraint using the “DROP CHECK” command is given below:

Output

The output showed that the “age_constraint” has been removed by using the “DROP CHECK” command.

Conclusion

The CHECK constraint in MySQL is used to specify the condition on a particular column that must be fulfilled while inserting or updating data in that column. It can be added or removed from an existing table using the ALTER command and can also be created while table creation. To add the CHECK constraint, the ADD CONSTRAINT clause is used, and to remove it, either the DROP CONSTRAINT or DROP CHECK clause can be utilized as shown in this guide.