Complex CHECK Constraints in SQL Server

Depending on your needs, SQL Server allows you to use a single CHECK constraint across multiple columns or numerous CHECK Constraints across a single column. The following examples show how it can be done.

Example-1

CREATE TABLE tbProducts(
ProductID INT IDENTITY PRIMARY KEY,
ProductName VARCHAR(255) NOT NULL,
PurchasedPrice DECIMAL(10,2),
SalablePrice DECIMAL(10,2),
DiscountedPrice DECIMAL(10,2),
CONSTRAINT Ck_DiscountedPrice_tbProducts1 CHECK (DiscountedPrice > PurchasedPrice),
CONSTRAINT Ck_DiscountedPrice_tbProducts2 CHECK (DiscountedPrice < SalablePrice)
);

Example-2:

CREATE TABLE tbProducts(
ProductID INT IDENTITY PRIMARY KEY,
ProductName VARCHAR(255) NOT NULL,
PurchasedPrice DECIMAL(10,2),
SalablePrice DECIMAL(10,2),
DiscountedPrice DECIMAL(10,2) CHECK (DiscountedPrice > 0),
CHECK (DiscountedPrice > PurchasedPrice)
);

Example-3:

CREATE TABLE ActiveCustomersSummary
(
SummaryID INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT NOT NULL,
CustomerName VARCHAR(50),
OrdersCount INT,
StartEffectiveDate DATE,
EndEffectiveDate DATE,
CONSTRAINT ck_ActiveStatus_ActiveCustomersSummary CHECK
(
(StartEffectiveDate < GETDATE() AND EndEffectiveDate > GETDATE())
OR OrdersCount > 1
)
);