SQL Check

Validating values with a condition

A CHECK constraint lets you write a boolean expression that every value must satisfy before it can be stored. If the expression evaluates to true (or to unknown, in the case of NULL), the value is accepted; if it evaluates to false, the database rejects the row. This is how you enforce business rules directly in the schema, such as prices never being negative or an age being at least eighteen.

Defining CHECK at table creation

CREATE TABLE Accounts (
    AccountID INT NOT NULL PRIMARY KEY,
    Balance   DECIMAL(12, 2) CHECK (Balance >= 0),
    Country   VARCHAR(2),
    Age       INT,
    CONSTRAINT chk_age CHECK (Age >= 18)
);

Balance uses an inline check that rejects negative numbers, and chk_age is a named table-level check that requires an age of at least eighteen. Naming the constraint makes it easy to drop or alter later.

Combining conditions

A single CHECK can combine several conditions using AND and OR, and it can reference more than one column. This is useful when the validity of one field depends on another.

CREATE TABLE Events (
    EventID   INT NOT NULL PRIMARY KEY,
    StartDate DATE NOT NULL,
    EndDate   DATE NOT NULL,
    CONSTRAINT chk_dates CHECK (EndDate >= StartDate)
);

This constraint guarantees an event can never end before it begins, because it compares two columns within the same row.

Adding or removing a CHECK later

-- Add a check to an existing table
ALTER TABLE Accounts
ADD CONSTRAINT chk_country
CHECK (Country IN ('US', 'CA', 'GB'));

-- Remove it
ALTER TABLE Accounts
DROP CONSTRAINT chk_country;