SQL CHECK Keyword

The CHECK constraint in SQL is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column, it allows only certain values for this column. If you define a CHECK constraint on a table, it can limit the values in certain columns based on values in other columns in the row.

---

1. Introduction to SQL CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy certain conditions. This constraint helps maintain the integrity of the data in the database by preventing invalid data entries.

2. Basic Syntax of CHECK Constraint

The CHECK constraint can be applied when creating a table or after the table has been created. Here is the basic syntax:

When Creating a Table

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

Adding to an Existing Table

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

3. Creating Tables with CHECK Constraint

Let’s create a table employees with a CHECK constraint.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL(10, 2)
);

4. Adding CHECK Constraint to Existing Tables

To add a CHECK constraint to an existing table employees:

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 30000);

5. Examples with Detailed Explanations

Example 1: Simple CHECK Constraint on a Single Column

Scenario: Ensure that the age of employees is between 18 and 65.

Code:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL(10, 2)
);

Explanation:

• id: Primary key of the table.
• name: Name of the employee.
• age: Must be between 18 and 65 due to the CHECK constraint.
• salary: Salary of the employee.

Example 2: CHECK Constraint on Multiple Columns

Scenario: Ensure that the salary is at least 30000 and age is between 18 and 65.

Code:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary DECIMAL(10, 2),
    CHECK (age >= 18 AND age <= 65 AND salary >= 30000)
);

Explanation:

• The CHECK constraint is applied to both age and salary columns.
• It ensures that age is between 18 and 65 and salary is at least 30000.

Example 3: Complex CHECK Constraints

Scenario: Ensure that the age is between 18 and 65, and if the role is ‘Manager’, the salary must be at least 50000.

Code:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary DECIMAL(10, 2),
    role VARCHAR(50),
    CHECK (age >= 18 AND age <= 65),
    CHECK (role <> 'Manager' OR salary >= 50000)
);

Explanation:

• The first CHECK ensures that age is between 18 and 65.
• The second CHECK ensures that if the role is ‘Manager’, then salary must be at least 50000.

6. Testing the CHECK Constraints

To see the CHECK constraints in action, let’s insert some data into the employees table.

Valid Insert:

INSERT INTO employees (id, name, age, salary, role) 
VALUES (1, 'John Doe', 30, 60000, 'Manager');

This insert succeeds because it satisfies all CHECK constraints.

Invalid Insert:

INSERT INTO employees (id, name, age, salary, role) 
VALUES (2, 'Jane Smith', 70, 40000, 'Employee');

This insert fails because the age is not between 18 and 65.

Another Invalid Insert:

INSERT INTO employees (id, name, age, salary, role) 
VALUES (3, 'Bill Turner', 45, 40000, 'Manager');

This insert fails because the salary for a Manager is less than 50000.

7. Best Practices and Considerations

• Keep Constraints Simple: Simple constraints are easier to understand and maintain.
• Combine Constraints When Necessary: Use multiple CHECK constraints when different rules apply to different columns.
• Ensure Valid Data: Always test your constraints with various data inputs to ensure they work as expected.
• Performance Impact: Be aware that complex constraints can impact the performance of data inserts and updates.

---

By following this guide, you should have a good understanding of how to use the SQL CHECK keyword to enforce data integrity rules within your database. Practice creating, modifying, and testing CHECK constraints to become proficient in using this powerful feature.