A CHECK constraint in SQL Server is a type of validation that is applied to a column or a set of columns in a table. It ensures that the data entered into the column(s) meets a specific condition or rule. If the condition is not met, the insertion or update of the data will fail and an error message will be returned.

Example of CHECK constraint

For example, a CHECK constraint can be used to ensure that the value of a "quantity" column is always greater than zero. The CHECK constraint would be defined as CHECK (quantity > 0).

USE model;
GO CREATE TABLE orders (
order_id INT PRIMARY KEY,
quantity INT NOT NULL CHECK (quantity > 0),
price DECIMAL(10,2) NOT NULL);
GO

If a user tries to insert or update a row with a quantity of zero or less, the operation will fail and an error message will be returned.

insert into orders(order_id, quantity, price)
values(1, 0, 10);

Add CHECK constraint

The example below shows how to add a CHECK constraint to an existing table. This type of constraint consisting of two conditioned columns is also called table level constraint.


ALTER TABLE orders
ADD CONSTRAINT CK_Q_P CHECK (quantity > 0 AND price > 0);

CHECK constraints can be used to enforce complex business rules, and can be defined at the column level or at the table level. They can also reference other columns in the same table, and can be combined with other types of constraints (such as UNIQUE or FOREIGN KEY constraints) to create a more robust validation system.

It is important to note that check constraint is enforced only on the statement execution not at the time of data modification.