• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Check

← Previous Next →

The MySQL CHECK constraint is a tool used to define rules that values in a table must meet. By applying a CHECK constraint, you ensure that only valid and meaningful data is stored in the database. This is essential for maintaining data integrity and minimizing errors in applications that depend on the database.

CHECK constraints are widely used in business applications, including student management, payroll, inventory, and e-commerce systems. They provide a first line of defense against invalid data before it reaches your application logic.

What Is a CHECK Constraint?

A CHECK constraint is a condition that must evaluate to TRUE for data to be accepted in a column or table.

Key Features:

  • Ensures data validity and enforces business rules.

  • Can be applied to a single column or multiple columns.

  • Automatically rejects inserts or updates that violate the condition.

  • Useful for numeric ranges, string values, dates, and logical expressions.

For example, in a student database, you might want to ensure that age is never less than 18 or that grades fall between 0 and 100.

Syntax for CHECK

Column-Level CHECK:

CREATE TABLE table_name (
    column_name datatype CHECK (condition),
    ...
);

Table-Level CHECK:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    CONSTRAINT constraint_name CHECK (condition)
);

Explanation:

  • column_name is the column to which the CHECK applies.

  • condition is a logical expression (e.g., age >= 18).

  • constraint_name is optional but recommended for easier reference and maintenance.

Creating a Table with CHECK Constraints

Example 1 – Column-Level CHECK:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18)
);

Explanation:

  • The age column cannot accept values below 18.

  • Any attempt to insert age < 18 will result in an error.

Example 2 – Table-Level CHECK:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary INT,
    department_id INT,
    CONSTRAINT chk_salary CHECK (salary > 0),
    CONSTRAINT chk_department CHECK (department_id BETWEEN 1 AND 10)
);

Explanation:

  • salary must be positive.

  • department_id must be between 1 and 10.

  • Table-level constraints allow complex rules involving multiple columns.

Adding CHECK Constraints to Existing Tables

You can also add a CHECK constraint to an existing table using ALTER TABLE:

ALTER TABLE students
ADD CONSTRAINT chk_age CHECK (age >= 18);

Explanation:

  • The constraint ensures future inserts or updates comply with the age rule.

  • If existing data violates the constraint, the command will fail, so it’s important to validate current data first.

Inserting and Updating Data with CHECK

Valid Insert Example:

INSERT INTO students (student_id, student_name, age)
VALUES (1, 'Ananya', 20);

Result: Success, because age is 20 (≥ 18).

Invalid Insert Example:

INSERT INTO students (student_id, student_name, age)
VALUES (2, 'Riya', 16);

Result: Error, because age does not satisfy the CHECK condition.

Updating Data Example:

UPDATE students
SET age = 15
WHERE student_id = 1;

Result: Error, because the new age violates the CHECK constraint.

Combining CHECK with Other Constraints

CHECK constraints are often combined with NOT NULL, UNIQUE, or PRIMARY KEY constraints to strengthen data integrity:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INT DEFAULT 0 CHECK (stock >= 0)
);

Explanation:

  • price cannot be negative.

  • stock cannot be negative.

  • Ensures that essential numeric fields remain logical and consistent.

Practical Use Cases

  1. Age Verification: Ensure users meet minimum age requirements.

  2. Salary Limits: Ensure employee salaries are above a minimum value.

  3. Score Validation: Restrict test scores to a valid range (0–100).

  4. Status Validation: Limit a status column to allowed values such as 'active' or 'inactive'.

  5. Inventory Management: Ensure quantities and prices are non-negative.

  6. Date Constraints: Restrict dates to a valid range (e.g., order_date cannot be in the past).

  7. Logical Rules Across Columns: For example, discount cannot exceed price.

Best Practices

  1. Use Descriptive Names: Easier for future maintenance.

  2. Define Constraints Early: Apply during table creation whenever possible.

  3. Validate Existing Data: Check current data before adding constraints to avoid failures.

  4. Combine with NOT NULL: Prevent NULL values from bypassing rules.

  5. Keep Conditions Simple: Complex expressions can be hard to maintain.

  6. Test Thoroughly: Always test inserts and updates for compliance with constraints.

Common Mistakes to Avoid

  1. Ignoring Existing Data: Adding CHECK constraints to tables with invalid data fails.

  2. Overcomplicating Conditions: Complex rules can create confusion and errors.

  3. Relying Only on Application Logic: Enforcing rules at the database level prevents inconsistencies.

  4. Forgetting NULL Handling: CHECK allows NULL values unless combined with NOT NULL.

  5. Not Naming Constraints: Makes future modification or removal difficult.

Summary of the Tutorial

The MySQL CHECK constraint is a powerful feature to enforce rules and maintain valid data.

  • Ensures that columns only accept values that meet defined conditions.

  • Can be applied at column or table level.

  • Works well with NOT NULL, UNIQUE, and PRIMARY KEY constraints.

  • Prevents invalid, inconsistent, or illogical data from entering the database.

  • Supports a wide range of business rules and practical scenarios.

Proper use of CHECK constraints keeps your database reliable, accurate, and aligned with business requirements.

Practice Questions

  1. Create a students table with a CHECK constraint to ensure age >= 18.

  2. Create an employees table with salary > 0 and department_id between 1 and 10 using CHECK constraints.

  3. Add a CHECK constraint to an existing products table to ensure price >= 0.

  4. Insert a row into the students table with age 16 and observe the result.

  5. Insert multiple rows into the employees table and ensure that all salary values comply with the CHECK constraint.

  6. Create a courses table with credits column using CHECK to ensure credits is between 1 and 5.

  7. Update a row in the students table to an age less than 18 and observe the error.

  8. Create a products table with stock >= 0 and price >= 0 using CHECK constraints.

  9. Add multiple CHECK constraints to an orders table to ensure order_date >= '2025-01-01' and quantity > 0.

  10. Create a grades table with score column using CHECK to ensure score is between 0 and 100.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top