✅ SQL CHECK Constraint – Validate Data Before It Enters Your Table

Want to make sure that only valid data is stored in your tables? The SQL CHECK constraint allows you to define rules that every row must follow—automatically rejecting any invalid data during INSERT or UPDATE.

This guide will help you understand the CHECK constraint in SQL, its syntax, real-world examples, and best practices.

---

📘 What is the CHECK Constraint in SQL?

The CHECK constraint enforces a specific condition that each row must satisfy. If a row fails the condition, the database throws an error and rejects the operation.

✅ Why Use CHECK?

• Enforce business rules at the data level
• Prevent invalid or out-of-range entries
• Minimize data quality issues early
• Enhance database reliability and trustworthiness

---

🧾 SQL CHECK Constraint Syntax

➤ In CREATE TABLE:

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  age INT,
  salary DECIMAL(10, 2),
  CHECK (age >= 18 AND age <= 60),
  CHECK (salary >= 15000)
);

➤ Named CHECK Constraint:

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  price DECIMAL(10, 2),
  CONSTRAINT chk_price CHECK (price > 0)
);

➤ Add CHECK Using ALTER TABLE:

ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age BETWEEN 18 AND 60);

---

🎯 Real-World Use Cases

Scenario	Example CHECK Condition
Employee age restriction	CHECK (age >= 18 AND age <= 60)
Positive product pricing	CHECK (price > 0)
Valid discount percentage	CHECK (discount BETWEEN 0 AND 100)
Valid enrollment year	CHECK (year_of_enrollment >= 2000)
Gender limited to ‘M’ or ‘F’	CHECK (gender IN ('M', 'F'))

---

⚠️ Important Notes

• CHECK conditions must evaluate to TRUE or UNKNOWN (for NULLs) — FALSE will reject the row.
• You can define multiple CHECK constraints on a table.
• Complex business logic may require using TRIGGERS or STORED PROCEDURES.

---

🧠 Common Mistake to Avoid

-- ❌ Wrong: Will reject NULL values
CHECK (salary > 0)

If you want to allow NULL but enforce the check only when value is present:

-- ✅ Correct: Allows NULL, checks only if not NULL
CHECK (salary IS NULL OR salary > 0)

---

📝 Summary

• The CHECK constraint validates the data before it is stored.
• Ideal for enforcing rules like age limits, positive numbers, value ranges, etc.
• It increases data quality, trustworthiness, and reduces application-side errors.
• Works alongside NOT NULL, UNIQUE, and PRIMARY KEY constraints for robust schema design.