SQL Check Constraint

A SQL Check Constraint is defined a condition on a column of table that must satisfy then it allows to insert or update for a column.

Here is an example: OrderAmount must be positive value

Syntax: Create Check Constraint on Create Table:

The below SQL script a Check Constraint on OrderAmount column and it will allow only positive value for OrderAmount.

CREATE TABLE Orders (

   OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    OrderAmount Decimal(10,2) Check(OrderAmount > 0) NOT NULL

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)

    REFERENCES OrderStatus(StatusID)

);

Basically Check Keyword is used to apply Check Constraint on a column and usually it is a logically expression

Check(OrderAmount > 0) NOT NULL

Syntax: Create Check Constraint on Alter Table:

The below SQL script a Check Constraint on table, which is already created

ALTER TABLE dbo.Orders

ADD CONSTRAINT OrderAmount_Ckeck Check(OrderAmount > 0);

Syntax: Drop Check Constraint:

The below SQL is used to drop the check constraint from Table

ALTER TABLE Orders

DROP CONSTRAINT OrderAmount_Ckeck;