WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

MySQL CHECK

Previous Next

MySQL CHECK Constraint

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