This write-up will present a profound understanding of including or eradicating the CHECK constraint in MySQL.
Tips on how to Add or Take away CHECK Constraint in MySQL?
The “CHECK” constraint in MySQL is utilized to specify the situation on a specific column that should be fulfilled whereas inserting or updating any information in that column. So as to add or take away the CHECK constraint in MySQL, the “ALTER TABLE” command may be utilized, which is used to switch the prevailing desk information.
Let’s transfer to the syntax of including or eradicating the CHECK constraint in MySQL.
The syntax so as to add the “CHECK” constraint in MySQL is given under:
ALTER TABLE [tab_name] ADD CONSTRAINT [const_name] CHECK ([condition]);
Right here on this syntax, “ADD CONSTRAINT” is used with the title “[const_name]” so as to add the CHECK constraint with a particular situation “[condition]“, to the desk named “[tab_name]“.
Furthermore, the “CHECK” constraint in MySQL will also be added whereas making a desk, to know this idea observe the given under syntax:
CREATE TABLE [table_name] (
columns,
CHECK ([condition])
);
Within the above syntax, the CHECK constraint is created as “CHECK ([condition])” and in that “[condition]” represents a specific situation/criterion.
The syntax to take away the “CHECK” constraint in MySQL is given under:
ALTER TABLE [table_name] DROP CONSTRAINT [constraint_name];
Within the above syntax, “DROP CONSTRAINT” is used with the title “[constraint_name]” to take away the CHECK constraint.
Let’s head towards the examples to see learn how to add or take away the CHECK constraint in MySQL.
How Do I Add the CHECK Constraint in MySQL?
So as to add the “CHECK” constraint in MySQL you need to have an present desk or you’ll be able to create a desk by following the given under instance:
CREATE TABLE lh_check (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(50) NOT NULL,
age INT NOT NULL,
e mail VARCHAR(100) NOT NULL,
gender ENUM(‘M’, ‘F’) NOT NULL,
PRIMARY KEY (id)
);
The given question will create a brand new desk named “lh_check”.
Output
The output clearly states that the desk has been created with out the CHECK constraint.
So as to add the CHECK constraint to the “lh_check” desk, the “ALTER TABLE” may be utilized. An instance of including the CHECK constraint title “age_constraint” that ensures the “age” column should be equal to or higher than 18 is given under:
ALTER TABLE lh_check
ADD CONSTRAINT age_constraint CHECK (age >= 18);
Output 
The output depicts that the CHECK constraint title “age_constraint” has been added.
Tips on how to Affirm Whether or not a CHECK Constraint is Added or Not in MySQL?
To verify whether or not a CHECK constraint is added or not, the “SHOW CREATE TABLE” command may be utilized in MySQL as follows:
SHOW CREATE TABLE lh_check;
Within the above command, the CHECK constraint is confirmed within the “lh_check” desk.
Output
The output “CONSTRAINT `age_constraint` CHECK ((`age` >= 18))” confirmed that the CHECK constraint has been added.
Insert Knowledge Into the Desk
Let’s insert information within the “lh_check” desk that doesn’t meet the situation of the “age_constraint” as given under within the command:
INSERT INTO lh_check (title, age, e mail, gender) VALUES
(‘John Doe’, 15, ‘[email protected]’, ‘M’);
Within the above command, the “age” worth is “15” however in accordance with the “age_constraint”, the worth of the “age” column should be equal to or higher than “18”.
Output
The output “ERROR 3819 (HY000): Verify constraint ‘age_constraint’ is violated.” confirmed that the worth can’t be inserted if the worth doesn’t meet the situation of the constraint.
Now let’s insert information within the “lh_check” desk that meets the situation of the “age_constraint” as given under within the command:
INSERT INTO lh_check (title, age, e mail, gender) VALUES
(‘John Doe’, 19, ‘[email protected]’, ‘M’);
Within the above command, the “age” worth is “19” which is larger than “18”.
Output
The output confirmed that the worth has been added, which suggests the worth can solely be inserted if it meets the situation of the verify constraint.
Tips on how to Create/Outline a CHECK Constraint in a Desk’s Column?
In MySQL, the “CHECK” constraint may be created to specify specific standards whereas making a desk. An instance to create the “CHECK” constraint whereas creating the “lh_check” desk is given under:
CREATE TABLE lh_check (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(50) NOT NULL,
age INT NOT NULL,
e mail VARCHAR(100) NOT NULL,
gender ENUM(‘M’, ‘F’) NOT NULL,
PRIMARY KEY (id),
CHECK (age >= 18)
);
Within the above instance, the CHECK constraint is “CHECK (age >= 18)” which suggests the situation is utilized to the “age” column.
Output
The output confirmed that the desk has been created with CHECK constraint.
How Do I Take away a CHECK Constraint From a Column in MySQL?
The CHECK constraint in MySQL may be eliminated through the use of the DROP CONSTRAINT together with the “ALTER TABLE” assertion.
An instance to take away the CHECK constraint utilizing the “DROP CONSTRAINT” command is given under:
ALTER TABLE lh_check
DROP CONSTRAINT age_constraint;
Output
The output confirmed that the CHECK constraint title “age_constraint” has been eliminated through the use of the “DROP CONSTRAINT” command.
An instance to take away the CHECK constraint utilizing the “DROP CHECK” command is given under:
ALTER TABLE lh_check
DROP CHECK age_constraint;
Output
The output confirmed that the “age_constraint” has been eliminated through the use of the “DROP CHECK” command.
Conclusion
The CHECK constraint in MySQL is used to specify the situation on a specific column that should be fulfilled whereas inserting or updating information in that column. It may be added or faraway from an present desk utilizing the ALTER command and will also be created whereas desk creation. So as to add the CHECK constraint, the ADD CONSTRAINT clause is used, and to take away it, both the DROP CONSTRAINT or DROP CHECK clause may be utilized as proven on this information.
