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 > 100Explanation: 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 < 100Explanation: 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 constraintExplanation: 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 > 500Explanation: 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.
Similar Reads
SQL Tutorial Structured Query Language (SQL) is the standard language used to interact with relational databases. Whether you want to create, delete, update or read data, SQL provides the structure and commands to perform these operations. SQL is widely supported across various database systems like MySQL, Oracl
8 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
Non-linear Components In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
Spring Boot Tutorial Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
Class Diagram | Unified Modeling Language (UML) A UML class diagram is a visual tool that represents the structure of a system by showing its classes, attributes, methods, and the relationships between them. It helps everyone involved in a projectâlike developers and designersâunderstand how the system is organized and how its components interact
12 min read
Python Variables In Python, variables are used to store data that can be referenced and manipulated during program execution. A variable is essentially a name that is assigned to a value. Unlike many other programming languages, Python variables do not require explicit declaration of type. The type of the variable i
6 min read
Spring Boot Interview Questions and Answers Spring Boot is a Java-based framework used to develop stand-alone, production-ready applications with minimal configuration. Introduced by Pivotal in 2014, it simplifies the development of Spring applications by offering embedded servers, auto-configuration, and fast startup. Many top companies, inc
15+ min read
Backpropagation in Neural Network Back Propagation is also known as "Backward Propagation of Errors" is a method used to train neural network . Its goal is to reduce the difference between the modelâs predicted output and the actual output by adjusting the weights and biases in the network.It works iteratively to adjust weights and
9 min read
Polymorphism in Java Polymorphism in Java is one of the core concepts in object-oriented programming (OOP) that allows objects to behave differently based on their specific class type. The word polymorphism means having many forms, and it comes from the Greek words poly (many) and morph (forms), this means one entity ca
7 min read