MySQL CHECK is an integrity constraint. The CHECK constraint is specifically used for restricting the input values that can be allowed to one or more columns in a table.
The CHECK constraint functionality can be utilized after version 8.0.16 and above. Before MySQL 8.0.16 version, the syntax of the CHECK constraint was acceptable although the functionality was ignored. This means, when using CHECK, syntactically it would be accepted by MySQL but the constraint is overlooked while the statement was executed or is simply ignored.
The latest versions of MySQL allow us to use the CHECK constraint syntactically as well as while executing the statement, which means if in case, the value doesn’t meet the CHECK constraint’s requirement then an error is thrown.
CHECK constraint using CREATE TABLE statement
We can put a CHECK constraint on a single column or multiple columns when we are creating a table.
Below is the syntax for the CHECK constraint while creating a table:
CREATE TABLE tablename (
Value1 datatype,
Value2 datatype,
Value3 datatype CHECK check_constraint,
CONSTRAINT check_name CHECK (constraint)
);Code language: SQL (Structured Query Language) (sql)For better understanding let’s take an example. We’ll create a table as Employee and will have four attributes, such as name, gender, age, and email. Out of these four values, we are going to put CHECK constraint on gender, specifically male(M) or female(F), or transgender(T).
CREATE TABLE employee(
Name varchar(50),
Age int,
Email varchar(50),
Gender char(1) CHECK (Gender in(‘M’,’F’,’T’))
);Code language: SQL (Structured Query Language) (sql)CHECK constraint using ALTER table statement
In cases where the table already exists, and we want to put a constraint over a column then it can be possible by putting a constraint on a column with ALTER Table. In our case, we’ll use the CHECK constraint.
Below is the syntax for creating a CHECK constraint using ALTER table:
ALTER TABLE tablename
ADD constraint_name CHECK (check_constraint);Code language: SQL (Structured Query Language) (sql)Let’s understand it with an example, we’ll take the employee table which we just created, having the name, age, email, and gender of the employees in which gender already has a check constraint. We need to restrict the age of our employees from 18 to 65. Let’s see how to write a statement for the same.
ALERT TABLE employee
ADD chk_age CHECK (Age >= 18 AND Age <=65);Code language: SQL (Structured Query Language) (sql)Insert data into the table and Check the constraint error
As we have created all the necessary CHECK constraints on the Employee table, now whenever we’ll insert data into the table then the constraints will also be checked before the data is inserted into the table by SQL.
For example, if we write the below statement for the employee table then MySQL will throw an error. As we have only allowed three input values to the Gender column which are F, M, and T for Female, Male, and Transgender respectively. But in the below example, even though the gender is technically correct still SQL will throw an error because we have allowed ‘F’ for female and no other value.
INSERT INTO employee
VALUES ('Anamika' , 34, 'ana@mail.co' , 'Female');Code language: SQL (Structured Query Language) (sql)The error below is the error that you’ll see whenever you’ll violate the constraint which is set for a particular column in a table. The best thing to do to resolve this error is to insert only those values which are within the range of the constraint which is set for the said column in that table.
ERROR 3819 (HY000): Check constraint ‘e_chk_1’ is violated.
View existing constraints on a particular table
If you want to check if a constraint or any constraint exists within the table then you can use the following command. This command will show a tabular output of all the constraint-related data for the table name you’ve passed in the statement, in our case we’ll use the employee table.
SELECT *
FROM information_schema.table_constraints
WHERE table_schema = schema()
AND table_name = 'employee';Code language: SQL (Structured Query Language) (sql)Remove/Drop CHECK constraint
To drop a check constraint from a particular column in a table we use ALTER table statement.
Let’s see an example to understand how it works. We’ll take the employee table which we are using in this tutorial, we currently have two CHECK constraints on the table. The first is the constraint is for the gender column and the second is for the age column. In the below example, we’ll drop the CHECK constraint on the age column.
ALTER TABLE employee
DROP constraint chk_age;Code language: SQL (Structured Query Language) (sql)Useful Resources:
https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html