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 Client ADD CONSTRAINT CHK_Status CHECK (Status = 'Active' OR Status = 'InActive' OR Status = 'NA')
To remove a CHECK constraint use
ALTER TABLE with the
ALTER TABLE Client DROP CONSTRAINT CHK_Status
This does not delete the column -- only the validation is removed.
In this example the CHECK constraint checks two values against each other.
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.