Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Check Constraints

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Check Constraint
The MySQL Check Constraint
The MySQL Check Constraint is a condition that can be applied to a column to ensure that the inserted or updated data in that column meets the specified condition. The database rejects the operation if the condition is not met to maintain data integrity.
Check Constraint with a Trigger
A trigger in MySQL is used to automatically execute a set of SQL statements in response to specific events in the database, such as an INSERT, UPDATE, or DELETE operation.
A check constraint with a trigger allows us to perform actions automatically based on data changes.
Example
Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement as shown below 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
Following query inserts values into CUSTOMERS table using the INSERT statement 
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);
The table obtained is as shown below 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Now, we will add a check constraint to ensure that the age of customers in the CUSTOMERS table should be greater than or equal to 18. Additionally, we will create a trigger that, when an attempt is made to insert a record with an age less than 18, it will raise an error and prevent the insertion 
-- Creating a Trigger
DELIMITER //
CREATE TRIGGER check_age_trigger
BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
   IF NEW.AGE < 18 THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Age must be 18 or older';
   END IF;
END;
//
DELIMITER ;
﻿
-- Adding a Check Constraint
ALTER TABLE CUSTOMERS
ADD CONSTRAINT check_age_constraint CHECK (AGE >= 18);
Output
We get the output as shown below 
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0
Adding Check Constraint on Single Column
We can apply a check constraint on a column by specifying the check constraint after the column name at the time of table creation.
Syntax
Following is the syntax to specify the check constraint on column 
CREATE TABLE table_name (
   column1 datatype(size),
   column datatype(size) constraint constraintName
   CHECK Check(columnName condition value),..., column datatype (size)
);
Example
In this example, we are creating a table named EMPLOYEES and specifying a column-level check constraint on one column 
CREATE TABLE EMPLOYEES(
   EID INT NOT NULL,
   NAME VARCHAR(40),
   AGE INT NOT NULL CHECK(AGE>=20),
   CITY VARCHAR(30),
   C_Phone VARCHAR(12) NOT NULL UNIQUE
);
We can verify if the check constraint is working correctly by inserting a value into the EMPLOYEES table which does not satisfy the condition 
INSERT INTO EMPLOYEES 
VALUES (1, 'John', 19, 'New York', '09182829109');
Output
The output obtained is as follows 
ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.
Adding Check Constraint on Multiple Columns
We can add check constraint on multiple columns of a table by specifying the constraints for each column after the column name.
Example
In the following example, we are creating a table named STUDENTS and specifying a column-level check constraint on multiple columns (AGE and FEE) 
CREATE TABLE STUDENTS(
   SID INT NOT NULL,
   NAME VARCHAR(20),
   AGE INT NOT NULL CHECK(AGE<=24),
   CITY VARCHAR(30),
   FEE NUMERIC NOT NULL CHECK(FEE>=15000)
);
Now, we can insert records, but if we attempt to insert a record that violates these constraints, the database will reject it.
Here, we are inserting a valid record 
INSERT INTO STUDENTS 
VALUES (001, 'Robert', 21, 'LA', 17000);
We can see in the output below that the insertion is successful because the age is within the allowed range, and the fee meets the specified condition 
Query OK, 1 row affected (0.01 sec)
In here, we are attempting to insert a record violating constraints 
INSERT INTO STUDENTS 
VALUES (002, 'James', 25, 'Barcelona', 10000);
We can see that the insertion fails since the age exceeds 24, violating the constraint.
ERROR 3819 (HY000): Check constraint 'students_chk_1' is violated.
Adding Check Constraint on an Existing Table
We can also add a check constraint on an existing table in MySQL by using the ALTER statement. We must ensure that the constraint satisfy for the existing records in the table.
Syntax
ALTER TABLE table_name 
ADD CONSTRAINT ConstraintName 
CHECK(ColumnName condition Value);
Example
In the following example, we are adding a check constraint to the AGE column of the CUSTOMERS table created above 
ALTER TABLE CUSTOMERS 
ADD CONSTRAINT Constraint_Age 
CHECK (AGE >= 21);
Output
Following is the output of the above code 
Query OK, 7 rows affected (0.04 sec)
Records: 7  Duplicates: 0  Warnings: 0
Dropping Check Constraint
We can remove an existing constraint by using the ALTER statement with the DROP statement.
Syntax
Following is the syntax to remove a constraint from the table 
ALTER TABLE table_name 
DROP CONSTRAINT constraint_set;
Example
Following example removes an existing constraint from the AGE column in the CUSTOMERS table created above 
ALTER TABLE CUSTOMERS 
DROP CONSTRAINT Constraint_Age;
Output
After executing the above code, we get the following output 
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
Check-Constraints Using a Client Program
We can also perform check-constraints using the client program.
Syntax
PHPNodeJSJavaPython
To Specify check-constraint on a field to validate the condition through a PHP program, we need to execute the "Create" statement using the mysqli function query() as follows 
$sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)"
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
if($mysqli->query($sql)){
    printf("Table created successfully...!\n");
}
//let's insert some records... whose age is greater than 20
$sql = "INSERT INTO EMPLOYEES VALUES(1, 'Jay', 30, 'Hyderabad', '223233')";
if($mysqli->query($sql)){
    printf("First record(age>20) inserted successfully...!\n");
}
$sql = "INSERT INTO EMPLOYEES VALUES(2, 'John', 35, 'Lucknow', '213032')";
if($mysqli->query($sql)){
    printf("Second record(age>20) inserted successfully...!\n");
}
//table record before inserting employee record whose age is less than 20;
$sql = "SELECT * FROM EMPLOYEES";
printf("Table records(before inserting emp record age<20): \n");
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("EId: %d, NAME: %s, AGE: %d, CITY %s, C_Phone %d",
        $row['EID'],
        $row['NAME'],
        $row['AGE'],
        $row['CITY'],
        $row['C_Phone']);
        printf("\n");
    }
}
//let's insert some records... whose age is less than 20
$sql = "INSERT INTO EMPLOYEES VALUES(3, 'Vinnet', 18, 'Hyderabad', '228151')";
if($mysqli->query($sql)){
    printf("Third record(age<20) inserted successfully...!\n");
}
$sql = "SELECT * FROM EMPLOYEES";
printf("Table records: \n");
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("EId: %d, NAME: %s, AGE: %d, CITY %s, C_Phone %d",
        $row['EID'],
        $row['NAME'],
        $row['AGE'],
        $row['CITY'],
        $row['C_Phone']);
        printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as shown below 
Table created successfully...!
First record(age>20) inserted successfully...!
Second record(age>20) inserted successfully...!
Table records(before inserting emp record age<20):
EId: 2, NAME: John, AGE: 35, CITY Lucknow, C_Phone 213032
EId: 1, NAME: Jay, AGE: 30, CITY Hyderabad, C_Phone 223233
PHP Fatal error:  Uncaught mysqli_sql_exception: Check constraint 'employees_chk_1' is violated. in D:\test\checkconstraints.php:46    
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Check Constraint

The MySQL Check Constraint

Check Constraint with a Trigger

Example

Output

Adding Check Constraint on Single Column

Syntax

Example

Output

Adding Check Constraint on Multiple Columns

Example

Adding Check Constraint on an Existing Table

Syntax

Example

Output

Dropping Check Constraint

Syntax

Example

Output

Check-Constraints Using a Client Program

Syntax

Example

Output