MySQL CHECK Constraint
MySQL CHECK Constraint
The CHECK constraint in MySQL is used to limit the values that can be inserted into a column by applying a condition.
It ensures data validity and integrity at the database level.
What is CHECK Constraint?
Enforces a rule/condition on column values
Data is inserted only if condition is TRUE
Prevents invalid or illogical data
Supported properly in MySQL 8.0.16+
Why Use CHECK Constraint?
- Ensures valid data
- Prevents wrong entries
- Reduces application-level validation
- Improves data integrity
Basic Syntax
Simple Example
Age must be 18 or above
Invalid Insert
- Error: CHECK constraint violated
CHECK Constraint on Multiple Conditions
- Ensures marks are between 0 and 100
Column-Level vs Table-Level CHECK
Column-Level CHECK
Table-Level CHECK
Adding CHECK Constraint Using ALTER TABLE
Dropping CHECK Constraint
- Constraint name is required
CHECK Constraint with ENUM-like Logic
- Restricts values to specific options
CHECK Constraint vs NOT NULL (Interview)
| Feature | CHECK | NOT NULL |
|---|---|---|
| Restricts values | Yes | No |
| Allows NULL | Yes | No |
| Validates logic | Yes | No |
| Complexity | High | Low |
Important Notes (Very Important for Interview)
CHECK constraints work only in MySQL 8.0.16+
Older versions ignore CHECK
Always verify MySQL version
Common Mistakes
- Using CHECK in old MySQL versions
- Forgetting constraint name
- Writing invalid logical conditions
- Expecting CHECK to replace application validation completely
Interview Questions & MCQs (Very Important)
Q1. What is CHECK constraint used for?
A) Creating index
B) Ensuring data validity
C) Speeding queries
D) Auto increment
Answer: B
Q2. CHECK constraint allows insertion when condition is:
A) FALSE
B) TRUE
C) NULL only
D) Zero
Answer: B
Q3. From which version MySQL supports CHECK constraint?
A) 5.6
B) 5.7
C) 8.0.16
D) 9.0
Answer: C
Q4. Which statement adds CHECK constraint?
A) ADD CHECK
B) INSERT CHECK
C) ALTER TABLE … ADD CONSTRAINT
D) MODIFY CHECK
Answer: C
Q5. CHECK constraint is evaluated:
A) After insert
B) Before insert
C) During insert/update
D) During select
Answer: C
Q6. Which of the following is a valid CHECK?
A) Valid
B) Invalid
Answer: A
Real-Life Use Cases
- Age validation
- Marks & percentage limits
- Salary constraints
- Status validation
- Quantity & price control
Summary
CHECK constraint enforces logical conditions
Prevents invalid data entry
Supported in MySQL 8.0.16+
Can be column-level or table-level
Very important for interviews & exams
