Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Using CHECK Constraints to Validate Data (CHECK Constraint Examples)
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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 )

Back to Tip of the Day List Next Tip