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.

1️⃣ 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+

2️⃣ Why Use CHECK Constraint?

✔ Ensures valid data
✔ Prevents wrong entries
✔ Reduces application-level validation
✔ Improves data integrity

3️⃣ Basic Syntax


 

4️⃣ Simple Example

Age must be 18 or above


Valid Insert ✅



 

Invalid Insert ❌



 

❌ Error: CHECK constraint violated

5️⃣ CHECK Constraint on Multiple Conditions



 

✔ Ensures marks are between 0 and 100

6️⃣ Column-Level vs Table-Level CHECK

🔹 Column-Level CHECK

age INT CHECK (age >= 18)

🔹 Table-Level CHECK

CHECK (salary > 0 AND age >= 18)

7️⃣ Adding CHECK Constraint Using ALTER TABLE


 

8️⃣ Dropping CHECK Constraint


 

📌 Constraint name is required

9️⃣ 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

SELECT VERSION();

1️⃣1️⃣ 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?

CHECK (marks BETWEEN 0 AND 100)

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

You may also like...