SQL CHECK Constraint

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.

Example

#

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.


Syntax

#

To add a CHECK constraint to an existing table use ALTER TABLE.

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 DROP option.

ALTER TABLE Client  
DROP CONSTRAINT CHK_Status

This does not delete the column -- only the validation is removed.


More Examples

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.


You may also like



Guides


vsn 3.1