Previous
Written by: rJTutorial Team | Last Updated:

SQL CHECK Constraint

The CHECK constraint is a powerful tool used to enforce domain integrity in your database. It ensures that any value entered into a column meets a specific condition before the database accepts it. Think of it as a gatekeeper that prevents "garbage data" from ever entering your tables, ensuring your data remains reliable and accurate over time.

Developer Tip: While you can validate data in your application code (like JavaScript or Python), adding a CHECK constraint at the database level provides a "final line of defense" that protects your data regardless of which application or user is accessing the database.

 

Key Features of SQL CHECK Constraint

  • Custom Validation: It allows you to define specific rules that aren't covered by simple data types (e.g., ensuring a price is never negative).
  • Multi-Column Logic: You can create rules that compare two different columns in the same row (e.g., ensuring a ShipDate is always after an OrderDate).
  • Automatic Enforcement: Once defined, the database engine automatically checks the condition during every INSERT and UPDATE operation.
  • Flexible Logic: It supports a wide range of operators, including >, <, =, BETWEEN, IN, and even complex pattern matching.
Best Practice: Always give your CHECK constraints meaningful names. If a constraint fails, the error message will include the name, making it much easier to debug which rule was violated.

 

SQL CHECK Constraint Syntax

Defining CHECK in Table Creation

When creating a new table, you can define a CHECK constraint directly next to the column definition. This is the most common way to enforce simple, single-column rules.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    Age INT CHECK (Age >= 18)
);
  • In this example, the database will reject any attempt to add an employee younger than 18, protecting the system from invalid data entry.
Common Mistake: Forgetting that CHECK constraints treat NULL values as "unknown." If a column allows NULL, a CHECK constraint will generally let the NULL through because the condition doesn't technically evaluate to "False." If you want to prevent both small numbers and empty values, use NOT NULL alongside CHECK.

CHECK with Multiple Conditions

Sometimes a rule depends on multiple factors. In these cases, you define the constraint at the table level (usually at the end of the CREATE TABLE statement).

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL(10,2),
    DiscountedPrice DECIMAL(10,2),
    Quantity INT,
    CHECK (Price > 0 AND Quantity >= 0),
    CHECK (DiscountedPrice <= Price)
);
  • The first check ensures we aren't selling items for free or with negative stock.
  • The second check ensures a business rule: the DiscountedPrice can never be higher than the original Price.

 

Adding a CHECK Constraint to an Existing Table

If you've already created a table and realize you need to enforce a new rule, you can use the ALTER TABLE command. It is highly recommended to name the constraint using the CONSTRAINT keyword.

ALTER TABLE Employees  
ADD CONSTRAINT CHK_EmployeeAge CHECK (Age >= 18);
Watch Out: When adding a CHECK constraint to an existing table, the database will verify all existing rows. If any current data violates the new rule, the ALTER TABLE command will fail.

 

Removing a CHECK Constraint

If business requirements change (for example, if the legal working age changes), you may need to remove or replace a constraint. This is why naming your constraints is so important.

-- For SQL Server / Oracle / MS Access
ALTER TABLE Employees  
DROP CONSTRAINT CHK_EmployeeAge;

-- For MySQL
ALTER TABLE Employees
DROP CHECK CHK_EmployeeAge;

 

Inserting Data into a Table with CHECK Constraint

Let's look at how the database reacts when we interact with these rules. When a CHECK condition evaluates to FALSE, the entire SQL statement is rolled back, and an error is returned.

-- This works perfectly
INSERT INTO Employees (EmployeeID, Age)  
VALUES (1, 25);  

-- This will trigger an error
INSERT INTO Employees (EmployeeID, Age)  
VALUES (2, 16);  
-- ❌ Error: The INSERT statement conflicted with the CHECK constraint "CHK_EmployeeAge".
Developer Tip: Use the IN operator within a CHECK constraint to simulate an "Enum" behavior. For example: CHECK (Status IN ('Active', 'Pending', 'Archived')). This ensures the column only contains specific, pre-approved strings.

 

Summary

  • The CHECK constraint acts as a logical filter for your data, ensuring only valid rows are saved.
  • It can handle logical operators like AND, OR, BETWEEN, and IN for complex validation.
  • Constraints can be applied to a single column or multiple columns at once.
  • Always name your constraints (e.g., CHK_ColumnName) to make future maintenance and debugging much easier.
  • If a row violates the condition, the database will reject the change and keep your data clean.
Previous