CHECK is a contraint that is placed on one or more columns.
CHECK specifies that the values must satisfy certain conditions.
This ensures that invalid data does not enter the column.
Adding an invalid value to a CHECK column will cause an error.
In this table, the Status column only accepts Active and InActive values.
CREATE TABLE Client (
Id INT IDENTITY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Country NVARCHAR(30),
Status NVARCHAR(12) NOT NULL,
CONSTRAINT CHK_Status
CHECK (Status = 'Active' OR Status = 'InActive')
)
If the Status column receives any other value an error will occur.
To add a CHECK constraint to an existing table use ALTER TABLE.
ALTER TABLE Customer
ADD CONSTRAINT CHK_Country
CHECK (Country = 'UK' OR Country = 'France' OR Country = 'Spain')
To remove a CHECK constraint use ALTER TABLE with the DROP option.
ALTER TABLE Customer
DROP CONSTRAINT CHK_Country
This does not delete the column -- only the validation is removed.
In this example the CHECK constraint checks two column values concurrently.
CREATE TABLE Measurement (
Id INT IDENTITY,
Temperature INT NOT NULL,
Pressure INT NOT NULL,
CONSTRAINT CHK_Measurement
CHECK (Temperature > 150 OR Pressure > 100)
)
A measurement can only be entered if Temperature or Pressure are above certain threshold values.
