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)
 
 
REVIEW
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8