MySQL CHECK Constraint: Definition, Syntax, and Examples

The CHECK constraint in MySQL is used to enforce rules on the values in a column. It ensures that the data entered into a table meets specific conditions.

Key Characteristics of CHECK Constraint

Ensures data validity by applying conditions to columns.
Applied during table creation or using ALTER TABLE.
If the data doesn't meet the condition, MySQL throws an error.
Can be used on one or multiple columns.
Supports logical conditions using operators (>, <, =, AND, OR, etc.).

Syntax for CHECK Constraint

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  CONSTRAINT constraint_name CHECK (condition)
);

CHECK on CREATE TABLE

Example

CREATE TABLE students (

student_id INT PRIMARY KEY,

student_name VARCHAR(50) NOT NULL,

age INT CHECK (age >= 18),

score INT CHECK (score BETWEEN 0 AND 100)

);

Explanation:

age must be 18 or older.
score must be within the range of 0 to 100.
If values do not meet these criteria, MySQL will return an error.

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

Example

CREATE TABLE students (

student_id INT PRIMARY KEY,

student_name VARCHAR(50) NOT NULL,

age INT,

score INT,

city VARCHAR(50),

CONSTRAINT chk_age_score CHECK (age >= 18 AND score >= 50)

);

Explanation:

chk_age_score → Ensures that:

Age is 18 or above.
Score is 50 or above.

CHECK on ALTER TABLE

Example

ALTER TABLE students

ADD CONSTRAINT chk_age CHECK (age >= 18);

Explanation:

A new constraint chk_age ensures that the student's age is 18 or above.

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

Example

ALTER TABLE students

DROP CHECK chk_age;

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.