A CHECK constraint protects data by allowing a column value only when it satisfies a Boolean expression. It works like a rule written in plain English: shipment methods must be “P” or “E,” and shipment costs must stay within an allowed range. section 19.1.4
Validating shipment data
CREATE TABLE shipment ( ShipmentID int NOT NULL, OrderID int NOT NULL, ShipmentCost decimal(5,2) NOT NULL CHECK (ShipmentCost BETWEEN 0.00 AND 999.99), ShipmentMethod char(1) NOT NULL CHECK (ShipmentMethod IN ('P', 'E')), TrackingNumber varchar(20) NOT NULL UNIQUE, ShipmentDate datetime NOT NULL DEFAULT (CURRENT_DATE()), CONSTRAINT PK_shipment PRIMARY KEY (ShipmentID), CONSTRAINT FK_shipment_orderheader FOREIGN KEY (OrderID) REFERENCES orderheader(OrderID) ); Here, both endpoints of the cost range are valid because BETWEEN includes its beginning and ending values. ShipmentMethod accepts only P or E. section 19.1.4
Use CHECK constraints for practical data-quality rules such as:
- Restricting PaymentYear to a reasonable range such as 2000 through 2100, preventing likely typos. section 23.1.2
- Allowing only positive PaymentAmount values, preventing negative payments. section 23.1.2
- Combining conditions that involve multiple columns in one larger expression; place such a table-level constraint after the column definitions. section 19.1.4
CREATE TABLE shipment (
ShipmentID int NOT NULL,
OrderID int NOT NULL,
ShipmentCost decimal(5,2) NOT NULL
CHECK (ShipmentCost BETWEEN 0.00 AND 999.99),
ShipmentMethod char(1) NOT NULL CHECK (ShipmentMethod IN ('P', 'E')),
TrackingNumber varchar(20) NOT NULL UNIQUE,
ShipmentDate datetime NOT NULL DEFAULT (CURRENT_DATE()),
CONSTRAINT PK_shipment PRIMARY KEY (ShipmentID),
CONSTRAINT FK_shipment_orderheader FOREIGN KEY (OrderID)
REFERENCES orderheader(OrderID)
);
Inspect the complete shipment definition to see CHECK constraints alongside NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY, and FOREIGN KEY constraints. section 19.1.4
