SQL CHECK Constraint

What is a CHECK Constraint?

The CHECK constraint is used to limit the values that can be placed in a column. It ensures that the data entered into a table meets certain criteria before being accepted into the database.

Why Use CHECK Constraints?

Using CHECK constraints helps maintain data integrity by preventing invalid data from being inserted into a table. For example, you can use a CHECK constraint to ensure that a column only accepts positive values or that a date is within a specific range.

Creating a CHECK Constraint

You can define a CHECK constraint when you create a table, or you can add it to an existing table using the ALTER TABLE command. Here’s how to do it:

Example: Creating a CHECK Constraint

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    FirstName varchar(255) NOT NULL,
    LastName varchar(255) NOT NULL,
    Age int CHECK (Age >= 18)
);

In this example, the CHECK constraint ensures that the Age column only accepts values that are 18 or older.

Adding a CHECK Constraint to an Existing Table

If you need to add a CHECK constraint to an existing table, you can use the ALTER TABLE command:

ALTER TABLE Employees
ADD CONSTRAINT CHK_Age CHECK (Age >= 18);

This command adds a CHECK constraint named CHK_Age to ensure that the Age column contains only values of 18 or above.

Using Multiple CHECK Constraints

You can apply multiple CHECK constraints to a single column or across multiple columns to enforce complex business rules. For example:

CREATE TABLE Products (
    ProductID int PRIMARY KEY,
    ProductName varchar(255) NOT NULL,
    Price decimal CHECK (Price > 0),
    Discount decimal CHECK (Discount >= 0 AND Discount <= Price)
);

In this table, the CHECK constraints ensure that the Price is greater than 0, and that the Discount is a positive number that does not exceed the product's price.

Removing a CHECK Constraint

If you need to remove a CHECK constraint from a table, use the ALTER TABLE command:

ALTER TABLE Employees
DROP CONSTRAINT CHK_Age;

This command removes the CHK_Age constraint from the Employees table.

Practical Example

Let’s consider a scenario where you have a table for storing student grades. You want to ensure that the grades are within a specific range:

CREATE TABLE StudentGrades (
    StudentID int PRIMARY KEY,
    Subject varchar(255) NOT NULL,
    Grade int CHECK (Grade >= 0 AND Grade <= 100)
);

In this setup, the CHECK constraint ensures that the Grade is between 0 and 100, preventing invalid data from being entered.

Conclusion

The CHECK constraint is a powerful tool for enforcing data integrity in your SQL database. By applying rules directly to your columns, you can ensure that the data stored in your tables meets specific criteria, reducing the risk of errors and improving the quality of your data.