MYSQL CHECK Constraint

MySQL is a very famous and widely used open-source RDBMS. It is used to store, retrieve, and manage structured data efficiently. It is used in both types of applications i.e. large and small scale applications. In MySQL, the CHECK constraint enforces a condition on the column(s) of a table. It makes sure that a specific type of data only gets inserted into the table.

In this article, we are going to explore various use cases of MYSQL check constraints. We are going to cover all the basic points with clear and concise examples along with their explanations.

MYSQL CHECK Constraint

In MYSQL, the Check constraint is used to impose conditions on what type of data to be inserted into our table. It helps in maintaining the accuracy and consistency of the data. It helps in avoiding the entry of data that does not follow our specified conditions.

NOTE :- MySQL does not support check constraints before version 8.0.16. All the versions prior to 8.0.16 , will throw you an error on applying CHECK constraint on the table.

Creating a table with CHECK Constraint

In this, we are heading to create a table in our database. We will create a table named ‘geeksforgeeks‘. We will apply a check constraint on two of its columns.

Query:

CREATE TABLE geeksforgeeks(
  id int PRIMARY KEY,
  name varchar(100),
  questions int,
  rank int,
   CONSTRAINT CHK_validEntry CHECK (rank <= 100 and questions >100)
 );

Explanation : After executing this query, we will have a table in our database. Any row inserted into the table will have to follow check constraint condition i.e. rank should be less than or equal to 100 and questions should be grater than 100. If any rows fails to satisfy any of the mentioned condition, then this will throw an error.

Now lets insert data to our table and display them.

Query:

--Data Insertion
INSERT INTO geeksforgeeks(id,name,questions,rank)
VALUES(01,'Vishu',150,10);
INSERT INTO geeksforgeeks(id,name,questions,rank)
VALUES(02,'Sumit',145,15);
INSERT INTO geeksforgeeks(id,name,questions,rank)
VALUES(03,'Aayush',140,20);
INSERT INTO geeksforgeeks(id,name,questions,rank)
VALUES(04,'Neeraj',120,40);

-- Displaying our table's data
SELECT * from geeksforgeeks;

Output:

Table – geeksforgeeks

Explanation : In the above example, we can notice that all the above records follow the condition imposed by check constraint. All the records have questions grater than 100 and rank less than or equal to 100. Therefore all the records are successfully added to the table.

Examples of CHECK Constraints

In this, we are going to explore how check constraint actually works in MYSQL. We will see how it will behave if have try to enter any records which do not follow its imposed condition.

Example 1: Inserting a Row with Rank Grater than 100

In this example, we are going to see how check constraint will handle an insertion of record which do not follow its imposed condition.

Query:

INSERT INTO geeksforgeeks(id,name,questions,rank)
VALUES(05,'Vivek',150,120);

Output:

rank > 100

Explanation: In the above image, we can clearly see than an error appeared. It because we have tried to enter a row with rank 120, which is grater than 100. Therefore failing to satisfy the check constraint condition will result in an error.

Example 2: Inserting a Row with Questions Less than 100

In this example, we will try to insert a row with questions less than 100. Like in the previous example, this will us an error too. In order to insert a row successfully into our table, we need to satisfy both the conditions.

Query:

INSERT INTO geeksforgeeks(id,name,questions,rank)
VALUES(05,'Vivek',15,12);

Output:

question < 100

Explanation: In the above image, we can clearly notice an error. This error is caused as we try to enter a row with question less than 100 which is against our imposed condition. Therefore it throws us an error, just like in did in the previous example.

Drop the CHECK constraint

In order to drop CHECK constraint from our existing table, we will be using ALTER and DROP clause. ALTER Clause is used to modify the structure of the table and DROP Clause is used remove a database object.

Query:

ALTER TABLE geeksforgeeks
DROP CONSTRAINT CHK_validEntry;

After successfully executing this query we can add any type of rows to our table. Lets try one of the above example, which was previous throwing us an error.

Query:

INSERT INTO geeksforgeeks(id,name,questions,rank)
VALUES(05,'Vivek',150,120);

--Displaying table's data
SELECT * FROM geeksforgeeks;

Output:

Drop the check constraint

Explanation: We have used the same query from ‘example 1’. Previously, this query throw us an error but now we have removed the check constraint, we can clearly see that it executed successfully and row has been inserted into out table.

Adding CHECK Constraints to an Existing Table

We are going to add a CHECK constraint to our existing table. As our table have no check constraint, we add one with the below query.

Query:

ALTER TABLE geeksforgeeks
ADD CONSTRAINT CHK_validEntry
CHECK (rank < 500);

Now if we try to enter any row with rank grater than 500 will throw us an error.

Query:

INSERT INTO geeksforgeeks(id,name,questions,rank)
VALUES(06,'Harsh',150,560);

Output:

rank > 500

Explanation: In the above image, we can clearly notice an error. This is due to our newly imposed check constraint condition i.e. rank should be less than 500. We can clearly see that we are trying to enter a new row with rank as ‘560’ which is clearly not following our condition.

Conclusion

Overall, check constraint is use to impose a condition on the data which are going to be inserted into our table. We can clearly prevent on adding irrelevant data to our table. We have seen many examples related to check constraints, how to remove check constraint from existing table and adding check constraint to the existing table. Now you can write all the queries related to check constraint with ease can can get the desired output.