SQL Server 2008: Administering Database Objects - Working with Tables (part 4) - Check Constraints

5. Check Constraints

You can use a check constraint to help enforce domain integrity by validating or checking the data that is being inserted into a column before accepting the value. For example, you can use a check constraint to verify that a valid range of dates are being inserted into a column. You can use any logical expression that returns True or False to create a check constraint.

The code in Listing 14 creates a check constraint on a table named CustInfo on the DateOfBirth column to verify that any date of birth is greater than 1/1/1900 and less than the current date.

Example 14. Syntax to Create a Check Constraint When Creating a Table

USE AdventureWorks2008
GO


--Drop the table if it currently exists
IF OBJECT_ID('dbo.CustInfo', 'U') IS NOT NULL
  DROP TABLE dbo.CustInfo;

CREATE TABLE CustInfo
(CustID int IDENTITY(1,1) PRIMARY KEY,
 DateOfBirth DATE
 CONSTRAINT ValidDateOfBirth
 CHECK (DateOfBirth > '1/1/1900' AND DateOfBirth < getdate()))

Now if you try to run the query INSERT INTO CustInfo (DateOfBirth) VALUES ('1/1/1899') to insert a date that is out of the valid date range defined by the check constraint in Listing 14, you will get the error message shown in Figure 9-6.

Figure 3. Error message returned when trying to insert an out-of-range date as defined by the check constraint in Listing 14

To create a check constraint on a table that has already been created, use the ALTER TABLE statement, as shown in Listing 15. The WITH NOCHECK option will allow the check constraint to be applied even if there are current records in the table that do not meet the requirements of the constraint.

Example 15. Syntax to Create a Check Constraint Using the ALTER TABLE Statement

USE AdventureWorks2008
GO

--Drop the table if it currently exists
IF OBJECT_ID('dbo.CustInfo', 'U') IS NOT NULL
  DROP TABLE dbo.CustInfo;

--Create the table
CREATE TABLE CustInfo
(CustID int IDENTITY(1,1) PRIMARY KEY,
DateOfBirth DATE)

--Alter the table to add the check constraint
ALTER TABLE CustInfo WITH NOCHECK
ADD CONSTRAINT ValidDateOfBirth
CHECK (DateOfBirth > '1/1/1900' AND DateOfBirth < getdate())

You also have the ability to disable check constraints to allow you to perform certain operations. You can disable check constraints using the same syntax as disabling foreign key constraints. For the exact syntax, see the previous "Foreign Key Constraints" section. To remove a check constraint from a table, you need to issue the ALTER TABLE statement with the DROP CONSTRAINT keywords followed by the name of the check constraint, as shown in Listing 16.

Example 16. Syntax for Removing a Check Constraint

ALTER TABLE CustInfo DROP CONSTRAINT ValidDateOfBirth