✅ SQL CHECK – Enforce Column Rules and Data Validations

🧲 Introduction – What is SQL CHECK?

The CHECK constraint in SQL is used to enforce custom conditions on column values. It helps maintain data quality by restricting the range, pattern, or logic of what can be inserted or updated in a column.

🎯 In this guide, you’ll learn:

• Syntax of the CHECK constraint
• Common use cases and expressions
• Best practices and DBMS behavior notes

---

✅ 1. Basic CHECK Syntax

CREATE TABLE employees (
  id INT PRIMARY KEY,
  age INT CHECK (age >= 18),
  salary DECIMAL(10,2) CHECK (salary > 0)
);

✅ Ensures age is at least 18 and salary is positive.

---

🧱 2. Named CHECK Constraints

CREATE TABLE products (
  price DECIMAL(10,2),
  CONSTRAINT chk_price_positive CHECK (price > 0)
);

✅ Naming helps in debugging and maintaining constraints.

---

🔄 3. Add CHECK to Existing Table

ALTER TABLE orders
ADD CONSTRAINT chk_total_positive
CHECK (total_amount >= 0);

✅ Adds validation rule after table creation.

---

🧠 4. Complex CHECK Conditions

CREATE TABLE students (
  grade CHAR(1) CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))
);

✅ Restricts values to a predefined set.

CHECK (discount <= price)

✅ Enforces a rule across multiple columns.

---

⚠️ 5. CHECK Limitations & Notes

• CHECK constraints evaluate row-by-row, not across rows
• Not supported in some older MySQL versions (prior to 8.0)
• Cannot reference values from other tables (use triggers instead)
• Violations raise an error and reject the statement

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use for domain rules and logical bounds	Putting business logic into applications only
Name constraints clearly	Allowing silent errors without constraint
Combine with NOT NULL, DEFAULT	Writing overly complex expressions

---

📌 Summary – Recap & Next Steps

CHECK constraints help enforce domain-specific business rules at the database level. They’re simple to define and provide strong data quality assurance.

🔍 Key Takeaways:

• Use CHECK to restrict allowed column values
• Works best with numeric ranges, sets, and logical expressions
• Combine with other constraints like NOT NULL and UNIQUE

⚙️ Real-World Relevance:
Used in billing, validation of form data, gradebooks, pricing systems, and regulated data systems.

---

❓ FAQ – SQL CHECK

❓ What does the CHECK constraint do?

✅ It restricts column values based on a logical condition.

❓ Can I use CHECK on multiple columns?

✅ Yes. You can create expressions involving multiple columns.

❓ What happens if a CHECK fails?

❌ The statement is aborted, and the error is returned.

❓ Is CHECK supported in all databases?

✅ Most modern SQL engines support it. MySQL did not fully support it before version 8.0.

---

Share Now :