CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column.
Here are a few examples of where a CHECK constraint can be useful when validating data. The following table structure is used for each CHECK constraint shown:
CREATE TABLE [dbo].[Customers] (
[CustomerID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50) NOT NULL,
[LastName] VARCHAR(50) NOT NULL,
[Password] VARCHAR(20),
[Gender] CHAR(1) NOT NULL,
[PhoneNumber] VARCHAR(50),
[CellNumber] VARCHAR(50),
[Age] INT NOT NULL,
[Address] VARCHAR(100),
[City] VARCHAR(50),
[State] CHAR(2),
[ZIPCode] VARCHAR(5)
)
GO
Used to Check for Allowed Values in a Column:
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT CK_Gender
CHECK ( [Gender] IN ('M', 'F', 'U') )
Used to Check if One of Two Columns Have Values:
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT CK_Contact_Information
CHECK ( [PhoneNumber] IS NOT NULL OR [CellNumber] IS NOT NULL )
Used to Check Range Values :
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT CK_Adults
CHECK ( [Age] >= 21 )
Used to Validate Format :
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT CK_ZIPCode
CHECK ( [ZIPCode] LIKE '[0-9][0-9][0-9][0-9][0-9]' )
Used to Check for Data Length :
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT CK_PasswordLength
CHECK ( LEN([Password]) BETWEEN 8 AND 20 )