MariaDB Check Constraint + Examples

In this MariaDB tutorial, we will discuss the use of MariaDB Check Constraint and look at several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Check Constraint
• MariaDB Check Constraint Error
• MariaDB Add Check Constraint
• MariaDB Remove Check Constraint
• MariaDB Can’t Drop Constraint Check That If It Exists
• MariaDB Show Check Constraint
• MariaDB Check Constraint Unique Constraint

MariaDB Check Constraint

Here we will learn how to use the MariaDB CHECK Constraint in the query, which is explained with the help of an illustrated example.

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 USA_PROGRAMMING_CLASS table by the following query:

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)
);

In the USA_PROGRAMMING_CLASS table, the data type of the TOTAL_STUDENT column is an integer. To have a positive total number of students, we have added the CHECK constraint in the following query:

CHECK(TOTAL_STUDENT > 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.
• If we create a CHECK constraint without a name then MariaDB will automatically create a name for the column_name.
• So, we can explicitly specify the name of the CHECK constraint by using the CONSTRAINT clause in the query.

So, here is the syntax of the CHECK constraint with the constraint_name:

YOUR_COLUMN_NAME DATATYPE 
CONSTRAINT CONSTRAINT_NAME
CHECK( COLUMN_NAME);

Here is a demonstrated example of the CHECK constraint by the following query:

EXAMPLE:

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)
);

insert into USA_PROGRAMMING_CLASS (SITES_NAME, TOTAL_STUDENT ) 
values ('google.co.jp', 80),
 ('macromedia.com', 31),
 ( 'ucoz.ru', 99),
 ('hostgator.com', 64),
 ('bluehost.com', 11),
 ('seesaa.net', 19),
 ('boston.com', 9),
 ('constantcontact.com', 87),
 ('wp.com', 88),
 ('domainmarket.com', 4);
 
SELECT * FROM USA_PROGRAMMING_CLASS; 

In the first query, we created a table called USA_PROGRAMMING_CLASS by using the CREATE TABLE statement. And we have also used the CHECK constraint on the TOTAL_COLUMN to have a value greater than 0 for the records.

In the second query, we have inserted some new records of the USA_PROGRAMMING_CLASS table by using the INSERT INTO statement. If we want to check new records have been executed by using the INSERT INTO statement then we can use the SELECT statement to retrieve it.

So, here we understood the method of the MariaDB CHECK constraint from the query.

Read: MariaDB Insert Into Select

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:

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)
);

INSERT INTO USA_PROGRAMMING_CLASS('www.quora.com',0);

SELECT * FROM USA_PROGRAMMING_CLASS;

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.

EXAMPLE:

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)
);

INSERT INTO USA_PROGRAMMING_CLASS (SITES_NAME,TOTAL_STUDENT)
VALUES ('quora.com',10);
 
SELECT * FROM USA_PROGRAMMING_CLASS; 

First, we have created a table called USA_PROGRAMMING_CLASS by using the CREATE TABLE statement. And we have used the CHECK constraint on the TOTAL_STUDENT column whose value should be greater than 0.

Second, during insertion time we used the INSERT INTO statement on the USA_PROGRAMMING_CLASS table and we have put the value of the TOTAL_STUDENT column greater than 0 which is currently 10 for the new records of the table.

So, the new insertion gets executed and we can use the SELECT statement to retrieve all records of the USA_PROGRAMMING_CLASS table.

In this sub-topic, we understand what type of error arises in the MariaDB CHECK constraint which is explained in detail from the query.

Read: MariaDB Greatest Function

MariaDB Add Check Constraint

We will use the ALTER TABLE statement to add the CHECK constraint in the query and which will be explained with the help of an illustrated example.

In MariaDB, the ALTER TABLE statement is used to add, modify, drop/delete columns from the table. It’s also used to rename columns of the table. Here is an illustrated example of the MariaDB Add Check Constraint by the following query:

EXAMPLE:

ALTER TABLE USA_PROGRAMMING_CLASS
ADD CONSTRAINT POSITIVE_TOTAL_CONSTRAINT
CHECK(TOTAL_STUDENT > 0);

In the preceding query, we have used the ALTER TABLE statement in the USA_PROGRAMMING_CLASS table to add the constraint of the TOTAL_STUDENT column. The constraint name is POSITIVE_TOTAL_CONSTRAINT of the TOTAL_STUDENT column.

We have to use the CHECK constraint of the TOTAL_STUDENT column so that the value should be greater than 0. This query will be executed for the resultset.

If we put any value as 0 or less than 0 then it will throw an error in the INSERT INTO statement of the USA_PROGRAMMING_CLASS table.

Here we have used the ALTER TABLE statement to add the CHECK constraint.

Read: MariaDB Insert If Not Exists

MariaDB Remove Check Constraint

In this section, we will use the MariaDB ALTER TABLE statement to remove the CHECK constraint in the query and which is explained with the help of an illustrated example.

As we know, the ALTER TABLE statement in MariaDB is used to add, delete /drop, and modify the columns of the table. It is also used to rename the columns of the table. Here is an illustrated example to remove the CHECK constraint by the following query:

EXAMPLE:

ALTER TABLE TABLE_NAME
DROP CONSTRAINT POSITIVE_TOTAL_STUDENT;

As we see in the above query, we have used the ALTER TABLE statement to drop the constraint name as POSITIVE_TOTAL_STUDENT from the USA_PROGRAMMING_CLASS table. Once the query is executed, so if we put the value of the TOTAL_STUDENTS columns as 0 in the INSERT INTO statement.

Then also, the INSERT INTO statement will accept the value as 0 and the query will be executed in the result set.

In this section, we have learned how to drop the constraint_name of the CHECK by using the ALTER TABLE statement.

Read: MariaDB Median – Complete Tutorial

MariaDB Can’t Drop Constraint Check That If It Exists

Here we will use ALTER TABLE statement with the IF EXISTS clause for dropping the CHECK constraint name in the query and which is shown by an example.

When the MariaDB EXISTS condition is used with an outer query, it is said to be “met” if the subquery delivers at least one record. In a SELECT, INSERT, UPDATE, or DELETE statement, it can be utilized.

Here is an illustrated example of the ALTER TABLE statement with the IF EXISTS clause by the following query:

EXAMPLE:

ALTER TABLE USA_PROGRAMMING_CLASS DROP CONSTRAINT IF EIXTS POSTIVE_TOTAL_STUDENT;

• In the query, by using the ALTER TABLE statement we will drop the constraint_name as POSITIVE_TOTAL_STUDENT from the USA_PROGRAMMING_CLASS table.
• With the help of the IF EXISTS clause, it will check whether the constraint name of this exists in the table but if the name exists then it will execute the query for the result set.
• If the constraint_name as POSITIVE_TOTAL_STUDENT doesn’t exist then it will produce a warning of the query.
• In this section, we have used the IF EXISTS clause with the ALTER TABLE statement to drop the constraint_name of the CHECK column in the query.

Read: MariaDB Drop Index + Examples

MariaDB Show Check Constraint

In this section, we will use two different methods to show the CHECK constraint in MariaDB and we will explain it with the help of an illustrated example.

If we want to check all constraints of the table, we can try any of the following methods shown below:

1ST METHOD:

Here is the syntax of the SHOW method by the following query:

SHOW CREATE TABLE your_table_name

The example of the SHOW method is given below:

SHOW CREATE TABLE USA_PROGRAMMING_CLASS;

In this query, we have used the SHOW statement with the CREATE TABLE statement of the USA_PROGRAMMING_CLASS table. It will show the structure and data types used for the column.

2nd METHOD: Using Information.Schema

Here is the syntax of using the INFORMATION_SCHEMA method to check the CHECK constraint by the following query:

SYNTAX:

SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_NAME = 'TABLE_NAME';

Here is an illustrated example of using the INFORMATION_SCHEMA method to see the CHECK constraint in the below following query:

EXAMPLE:

SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_NAME = 'USA_PROGRAMMING_CLASS';

As we see in the above query, we have used the INFORMATION_SCHEMA method on the USA_PROGRAMMING_CLASS table to see all the constraints of the table. And also to see the CHECK constraint name from the table.

In this sub-section, we have used the SHOW and INFORMATION_SCHEMA methods to show the constraint_name of the CHECK column in the query.

Read: MariaDB Delete From Statement

MariaDB Check Constraint Unique Constraint

In this section, we will use the CHECK constraint and the UNIQUE constraint in the same query and which is explained with the help of an illustrated example.

In MariaDB, a UNIQUE constraint is a consistency constraint that guarantees the uniqueness of values in a field or set of columns. Here is an illustrated example of the CHECK constraint with the UNIQUE constraint by the following query:

EXAMPLE:

CREATE TABLE USA_PROGRAMMING_CLASS (
	STUDENT_ID INT AUTO_INCREMENT PRIMARY KEY,
	SITES_NAME VARCHAR(50) UNIQUE,
	TOTAL_STUDENT  INT,
	CONSTRAINT POSITIVE_TOTAL_STUDENT
	CHECK(TOTAL_STUDENT > 0)
);
 

• In this query, we have created a table called USA_PROGRAMMING_CLASS. In the table, we have used the UNIQUE constraint on the SITES_NAME column to get unique values for the table.
• And we have also used the CHECK constraint for getting the value of the TOTAL_STUDENT column greater than 0.
• So, during the INSERT INTO statement, we have to keep in mind that the value of the TOTAL_STUDENT column should be greater than 0 and the string name in the SITES_NAME column should be unique.
• If the SITES_NAME contains repeated string names then it will throw an error and the query will be not executed because the CONSTRAINT does not match the condition.

In this section, we have used the CREATE TABLE statement to add CHECK and UNIQUE constraints for the column in the query.

You may also like to read the following MariaDB tutorials.

• MariaDB Date Add Days
• MariaDB Not Equal Operator
• MariaDB Check String Length
• MariaDB Check If Rows Exists
• MariaDB Add Auto Increment Column
• MariaDB Delete From Statement
• MariaDB GROUP BY with Example
• MariaDB JSON Function + Examples

So, in this MariaDB tutorial, we have discussed how to use the MariaDB CHECK constraint and discussed several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Check Constraint
• MariaDB Check Constraint Error
• MariaDB Add Check Constraint
• MariaDB Remove Check Constraint
• MariaDB Can’t Drop Constraint Check That If It Exists
• MariaDB Show Check Constraint
• MariaDB Check Constraint Unique Constraint