Programming4us
         
 
 
SQL Server

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

2/10/2011 5:29:03 PM

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

NOTE

A NULL value evaluates to Unknown instead of True or False, so if you try to insert a NULL value into a column with a check constraint, the insert will succeed.

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
Other -----------------
- SQL Server 2008: Administering Database Objects - Working with Tables (part 2) - Primary Key Constraints & Unique Constraints
- SQL Server 2008: Administering Database Objects - Working with Database Snapshots
- Programming with SQL Azure : WCF Data Services (part 3)
- Programming with SQL Azure : WCF Data Services (part 2) - Creating the Client Application
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 4) - EXPLICIT Mode
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 3) - AUTO Mode
- Programming with SQL Azure : WCF Data Services (part 1)
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 2) - Working with Binary Columns
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 1) - RAW Mode
- Programming with SQL Azure : Connecting to SQL Azure (part 4) - Sqlcmd
- Programming with SQL Azure : Connecting to SQL Azure (part 3) - ODBC
- Programming with SQL Azure : Connecting to SQL Azure (part 2)
- Programming with SQL Azure : Connecting to SQL Azure (part 1) - ADO.NET
- Programming with SQL Azure : Application Deployment Factors
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 3)
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 2)
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 1)
- SQL Server 2008: SQL Server Web Services
- SQL Server 2008: SQL Server Service Broker - Related System Catalogs
- SQL Azure Backup Strategies (part 2)
 
 
Most View
- Security in Cloud Computing (part 2) - Identity and Access Management
- SQL Server 2008 : Failover Clustering
- SQL Server 2008: Administering Database Objects - Working with Tables (part 4) - Check Constraints
- Processing and Storing Data in SQL Server 2005 : Updating the FileWorker Class
- Exchange Server 2010 : Manage Access for Mobile Devices (part 1) - Configure Mobile Device Connectivity
- Windows Server 2008 : Configuring Terminal Services (part 1)
- Windows Phone 7 : Managing Mail Folders
- Microsoft Dynamic AX 2009 : The Batch Framework (part 6) - Managing the Batch Server Execution Process - Manage Batch Jobs, Debug a Batch Task
- Windows Phone 7 Game Development : The World of 3D Graphics - The Depth Buffer
- Windows 7: Troubleshooting Tools (part 2) - Running the Memory Diagnostics Tool
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS