CHECK Constraint in SQL

The CHECK constraint ensures that the values in a column meet a specific condition. It is used to enforce business rules at the database level, ensuring data integrity.

---

Syntax of CHECK Constraint

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

or

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

Supported in: SQL Server, PostgreSQL, MySQL, Oracle

---

Basic Example of CHECK Constraint

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT CHECK (Age >= 18)  -- Ensures Age is 18 or above
);

✅ Effect:

• Prevents inserting students below 18 years of age.

---

Variants of CHECK Constraint

1. CHECK with Multiple Conditions

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary INT,
    Age INT,
    CHECK (Salary > 3000 AND Age >= 18)  -- Enforces salary and age rules
);

✅ Effect:

• Ensures salary above 3000 and age 18 or above.

---

2. Naming the CHECK Constraint

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    Quantity INT,
    CONSTRAINT chk_quantity CHECK (Quantity > 0)  -- Named constraint
);

✅ Effect:

• Prevents orders with zero or negative quantity.

---

Using CHECK with ALTER TABLE

You can add a CHECK constraint after table creation:

ALTER TABLE Employees  
ADD CONSTRAINT chk_salary CHECK (Salary >= 5000);

Or drop a CHECK constraint:

ALTER TABLE Employees  
DROP CONSTRAINT chk_salary;

✅ Effect:

• Adds or removes a rule enforcing a minimum salary.

---

What Happens if the CHECK Condition Fails?

INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'John', 16);

❌ Error: CHECK constraint failed: Age >= 18

---

Real-Life Analogy

Think of the CHECK constraint like a gatekeeper at a club:

• Rule: Only people 18+ are allowed.

• If someone under 18 tries to enter, they are stopped.

Similarly, CHECK prevents invalid data from entering the database.

---

Key Points

1. CHECK enforces rules at the column or table level.

2. It ensures data integrity by preventing invalid entries.

3. Multiple conditions can be added using AND/OR.

4. CHECK constraints can be added or removed using ALTER TABLE.

5. If a record violates the CHECK condition, the query fails.