SQL - Check Constraint

SQL CHECK Constraint

What is the SQL CHECK Constraint?

The SQL CHECK constraint is a powerful tool for enforcing data integrity at the database level. It allows you to define a specific rule or condition that the data in a column (or multiple columns) must satisfy.

When a CHECK constraint is active, the database validates any new data from an INSERT statement or modified data from an UPDATE statement against this condition. If the data fails the check, the database rejects the operation and returns an error. This prevents invalid data from ever entering your table, ensuring higher data quality and consistency.

For example, in an Employees table, you could add a CHECK constraint to the Salary column to ensure it’s always a positive value, or to the EndDate column to ensure it is always later than the StartDate.

Defining CHECK Constraints (Column-Level)

The most straightforward way to add a CHECK constraint is at the column level, directly within the column’s definition when creating a table.

Syntax

CREATE TABLE TableName (
    ColumnName1 DataType [CONSTRAINT ConstraintName] CHECK (Condition),
    ColumnName2 DataType,
    ...
);

Note: While naming the constraint is optional in some databases, it is a strong best practice. A clear name makes it much easier to identify and manage the constraint later.

Example: Single Column Constraint

Let’s create a Users table. We’ll add a CHECK constraint to the Age column to ensure that users are at least 18 years old.

-- Creating the table with a named CHECK constraint
CREATE TABLE Users (
    ID INT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL UNIQUE,
    Age INT NOT NULL CONSTRAINT CHK_Users_Age CHECK (Age >= 18),
    Email VARCHAR(100)
);

Verification

Now, let’s test the constraint. An attempt to insert a user with an age less than 18 should fail.

-- This statement will SUCCEED
INSERT INTO Users (ID, Username, Age, Email) VALUES (1, 'alex_morgan', 25, 'alex@example.com');

-- This statement will FAIL
INSERT INTO Users (ID, Username, Age, Email) VALUES (2, 'casey_jones', 17, 'casey@example.com');

The second INSERT statement will produce an error message, typically mentioning the constraint name that was violated, like:

-- Example error from PostgreSQL
ERROR: new row for relation "users" violates check constraint "chk_users_age"

-- Example error from SQL Server
The INSERT statement conflicted with the CHECK constraint "CHK_Users_Age".

Defining CHECK Constraints (Table-Level)

When a constraint needs to reference multiple columns, it must be defined at the table level. This is common for rules that compare values within the same row.

Syntax

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    ...,
    [CONSTRAINT ConstraintName] CHECK (Condition involving multiple columns)
);

Example: Multi-Column Constraint

Let’s create a Products table. We want to ensure that the SalePrice is always less than or equal to the ListPrice.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    ListPrice DECIMAL(10, 2) NOT NULL,
    SalePrice DECIMAL(10, 2) NOT NULL,
    CONSTRAINT CHK_Products_Price CHECK (SalePrice <= ListPrice)
);

Verification

Attempting to insert a product where the sale price is higher than the list price will now fail.

-- This statement will SUCCEED
INSERT INTO Products (ProductID, ProductName, ListPrice, SalePrice) VALUES (101, 'Pro Widget', 100.00, 90.00);

-- This statement will FAIL
INSERT INTO Products (ProductID, ProductName, ListPrice, SalePrice) VALUES (102, 'Faulty Widget', 50.00, 55.00);

The second insert will be rejected by the database due to the CHK_Products_Price constraint.

Adding a CHECK Constraint to an Existing Table

You can add constraints to a table at any time using the ALTER TABLE statement. This is useful for enforcing new business rules on existing data.

Syntax

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName CHECK (Condition);

Warning: When you add a CHECK constraint to a table that already contains data, most database systems will immediately check all existing rows. If any row violates the new constraint, the ALTER TABLE statement will fail.

Example

First, let’s create a Orders table without a constraint.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    ShipDate DATE
);

Now, let’s add a constraint to ensure the shipping date is on or after the order date.

ALTER TABLE Orders
ADD CONSTRAINT CHK_Orders_ShipDate CHECK (ShipDate >= OrderDate);

Naming and Managing CHECK Constraints

To view existing constraints on a table, you can query the standard INFORMATION_SCHEMA. This is a portable method that works across many database systems like PostgreSQL, SQL Server, and MySQL.

SELECT
    constraint_name,
    check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'public' -- Adjust schema name if needed
  AND table_name = 'products';

Output

Running the above query against our Products table would yield:

constraint_name	check_clause
chk_products_price	((saleprice <= listprice))

Common Pitfalls and Best Practices

• CHECK vs. NULL: A CHECK constraint condition evaluates to UNKNOWN (not TRUE or FALSE) if any of the columns in the condition are NULL. In standard SQL, UNKNOWN allows the row to be inserted or updated. If you want to ensure a column is not NULL and also satisfies a condition, you need both a NOT NULL constraint and a CHECK constraint.
• Naming is Crucial: Always give your constraints a meaningful name (e.g., CHK_TableName_ColumnName). Unnamed, system-generated constraint names (like products_saleprice_check or CK__Products__456DEA_...) are difficult to remember and manage.
• Keep it Simple: CHECK constraints should enforce simple, stable business rules. Complex logic is often better handled in the application layer or database triggers/functions, as constraints can be difficult to debug and modify.
• Performance: CHECK constraints are evaluated for every INSERT and UPDATE on the constrained columns. While typically very fast, extremely complex checks can add overhead.

Dropping a CHECK Constraint

If a business rule changes, you can remove a CHECK constraint using the ALTER TABLE ... DROP CONSTRAINT statement. This is where having a clear, predictable name becomes essential.

Syntax

The syntax is generally consistent across major databases.

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;

Example

Let’s remove the age check from our Users table.

-- Dropping the constraint from the Users table
ALTER TABLE Users
DROP CONSTRAINT CHK_Users_Age;

Verification

After dropping the constraint, you can successfully insert a user with an age below 18.

-- This statement will now SUCCEED
INSERT INTO Users (ID, Username, Age, Email) VALUES (2, 'casey_jones', 17, 'casey@example.com');

Cross-Database Compatibility

The CHECK constraint is part of the SQL standard and is widely supported. However, there are minor differences:

• MySQL: Support for CHECK constraints was properly implemented in version 8.0.16. In earlier versions, the syntax was accepted but the constraint was not enforced.
• PostgreSQL and SQL Server: Both have robust, long-standing support for CHECK constraints with standard behavior.
• Oracle: Fully supports CHECK constraints.
• SQLite: Fully supports CHECK constraints.