MariaDB CHECK Constraint

MariaDB Server is one of the most popular open-source relational databases. It’s made by the original developers of MySQL and guaranteed to stay open source. It is part of most cloud offerings and the default in most Linux distributions.

MariaDB Server turns data into structured information in a wide array of applications, ranging from banking to websites. Originally designed as an, enhanced, drop-in replacement for MySQL, MariaDB Server is used because it is fast, scalable, and robust, with a rich ecosystem of storage engines, plugins, and many other tools that make it very versatile for a wide variety of use cases.

MariaDB is widely used to maintain high compatibility with open-source, with exact matching with MySQL APIs and commands. It includes new storage engines like Aria, ColumnStore, and MyRocks.

CHECK Constraint

A CHECK constraint verifies that a value in a column or collection of columns meets a Boolean expression before adding or upgrading it. The general syntax of the MariaDB CHECK constraint by the following query:

Syntax:

YOUR_COLUMN_NAME DATA TYPE CHECK(CONSTRAINT);

Now, let’s see the Content_Writing_Classes table by the following query:

CREATE TABLE Content_Writing_Classes 
(
    STUDENT_ID INT AUTO_INCREMENT PRIMARY KEY,
    SITES_NAME VARCHAR(50),
    TOTAL_ADMISSIONS INT,
    CONSTRAINT POSITIVE_TOTAL_ADMISSIONS
    CHECK(TOTAL_ADMISSIONS> 0)
);

Explanation: In the Content_Writing_Classes table, the data type of the TOTAL_ADMISSIONS column is an integer. To have a positive total number of admissions, we have added the CHECK constraint in the following query:

CHECK(TOTAL_ADMISSIONS > 0);

The check constraint appears after the data type in the column description. The check keyword is preceded by a Boolean expression enclosed in brackets.

Once we have used the check constraint is placed then we can’t insert or update a value to the column which will evaluate the condition to be false.

Constraints provide restrictions on the data you can add to a table. This allows you to enforce data integrity from MariaDB, rather than through application logic. When a statement violates a constraint, MariaDB throws an error.

There are four types of table constraints:

Constraint	Description
PRIMARY KEY	Sets the column for referencing rows. Values must be unique and not null.
FOREIGN KEY	Sets the column to reference the primary key on another table.
UNIQUE	Requires values in column or columns only occur once in the table.
CHECK	Checks whether the data meets the given condition.

MariaDB Check Constraint Error

We’ll understand what type of ERROR arises from the CHECK constraint in the query and which will be explained with the help of a demonstrated example.

In MariaDB, the CHECK constraint will provide an ERROR only when we use other values apart from the expression. Here is an example of the CHECK constraint error by the following query:

Error Example:

Firstly, created a table called USA_PROGRAMMING_CLASS

create table USA_PROGRAMMING_CLASS (
    STUDENT_ID INT AUTO_INCREMENT PRIMARY KEY,
    SITES_NAME VARCHAR(50),
    TOTAL_STUDENT  INT,
    CONSTRAINT POSITIVE_TOTAL_STUDENT
    CHECK(TOTAL_STUDENT > 0)
);

# Inserted a constraint in the table
INSERT INTO USA_PROGRAMMING_CLASS('www.quora.com',0);

# Select all from the table USA_PROGRAMMING_CLASS
SELECT * FROM USA_PROGRAMMING_CLASS;

Output:

Check Constraint Error

Explanation: First, we have created a table called USA_PROGRAMMING_CLASS by using the CREATE TABLE statement. In the CREATE TABLE statement, we have already mentioned that we will check that the value of the TOTAL_STUDENT column should be greater than 0 while using the CHECK constraint.

If we put the value of the TOTAL_STUDENTS as 0 then it will throw an error as per MariaDB documentation. This part mistake was done in the INSERT INTO statement for the USA_PROGRAMMING_CLASS table. Due to this, the SELECT statement wasn’t available to execute.

Define a Check Constraint That References to Multiple Columns

A check constraint can refer to multiple columns. In this case, you must define the check constraint as a table constraint.

See the following new sunshine table:

Create Table Sunshine

Query:

constraint due_date
CHECK (end_date >= begin_date)

Explanation: In this example, the due_date check constraint specifies that the end date must be the same or later than the beginning date:

Adding CHECK Constraints to an Existing Table

The alter table add constraint statement allows you to add a check constraint to an existing table:

Syntax:

ALTER TABLE table_name
ADD constraint constraint_name 
CHECK (expression);

Output:

Add Check Constraint

Explanation: For example, the following example adds a check constraint to the sunshine table:

Remove Check Constraints from an Existing Table

To remove a check constraint from a table, you use the alter table drop constraint statement:

Query:

ALTER TABLE table_name
DROP constraint constraint_name;

Output:

Remove Check Constraint.

Explanation: The following example drops the due_begin_date constraint.

Conclusion

MariaDB Server is a general purpose open source relational database management system. It’s one of the most popular database servers in the world. MariaDB Server is released under the GPLv2 open source license and is guaranteed to remain open source. It can be used for high availability, transaction data, analytics, as an embedded server, and a wide range of tooling and applications support MariaDB Server.
So, In this article, we have discussed about Check Constraints, how to create it, add Check Constraints, remove Check Constraints and several examples related to it.

Article Tags :

Databases

Geeks Premier League

MariaDB

Geeks Premier League 2023