A CHECK constraint in SQL Server is a rule that enforces domain integrity by limiting the values that can be entered into a column or set of columns in a table. It ensures that the data stored in a column meets specific conditions, enhancing data accuracy and consistency.

Key Features of a CHECK Constraint

Validation Rule: CHECK constraints define a logical condition that must be satisfied for the data to be inserted or updated in the column.

Custom Conditions: The condition can involve one or more columns and can include comparison operators (=, <glt;, >, <, >=, <=) and logical operators (AND, OR, NOT).

Enforcement at the Database Level: CHECK constraints are enforced by the database engine, providing an additional layer of data validation that complements application-level validations.

Syntax

CREATE TABLE table_name (
    column_name data_type,
    CONSTRAINT constraint_name CHECK (condition)
);

Or, define it inline:

CREATE TABLE table_name (
    column_name data_type CHECK (condition)
);

Examples

Basic Example Restrict a column to accept only positive values:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL(10, 2) CHECK (Price > 0)
);

Multiple Columns Ensure the total stock value remains within a specified range:

CREATE TABLE Inventory (
    ItemID INT PRIMARY KEY,
    Quantity INT,
    UnitPrice DECIMAL(10, 2),
    CONSTRAINT chk_stock_value CHECK (Quantity * UnitPrice <= 10000)
);

Custom Conditions Limit values to a predefined set:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    JobTitle NVARCHAR(50),
    CONSTRAINT chk_job_title CHECK (JobTitle IN ('Manager', 'Developer', 'Analyst'))
);

Key Points to Remember

Multiple Constraints: A table can have multiple CHECK constraints, and they can apply to the same or different columns.

NULL Values: CHECK constraints do not validate rows where the column's value is NULL since the condition evaluates to UNKNOWN.

Performance Impact: Although efficient, complex conditions in CHECK constraints can slightly impact performance during inserts and updates.

Conflicts: If a CHECK constraint conflicts with the data being inserted or updated, the operation fails, and an error is returned.

Managing CHECK Constraints

Adding a Constraint

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

Dropping a Constraint

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Disabling and Enabling Disable the constraint temporarily for bulk operations:

ALTER TABLE table_name
NOCHECK CONSTRAINT constraint_name;

Re-enable it afterward:

ALTER TABLE table_name
CHECK CONSTRAINT constraint_name;

Benefits

Improves data quality by preventing invalid data.
Reduces the need for additional validation logic in applications.
Helps maintain database consistency and integrity.

Limitations

Complex validations involving external data or cross-table references cannot be implemented with CHECK constraints.
They cannot call functions or reference subqueries.

By leveraging CHECK constraints effectively, you can enforce robust data validation rules directly within your SQL Server database.