Home

Transact-SQL Operators: CHECK

   

Introduction

The ability to verify that one or more rules are respected on a table is called a check constraint. A check constraint is a Boolean operation performed by the SQL interpreter. The interpreter examines a value that has just been provided for a column.

To create a check constraint in SQL, first create the column on which the constraint will apply. Before the closing parenthesis of the table definition, use the following formula:

CONSTRAINT name CHECK (expression

The CONSTRAINT and the CHECK keywords are required. As an object, make sure you provide a name for it. Inside the parentheses that follow the CHECK operator, enter the expression that will be applied. Here is an example that will make sure that the hourly salary specified for an employee is greater than 12.50:

CREATE TABLE Employees
(
	[Employee Number] nchar(7),
	[Full Name] varchar(80),
	[Hourly Salary] smallmoney,
	CONSTRAINT CK_HourlySalary CHECK ([Hourly Salary] > 12.50)
);

It is important to understand that a check constraint it neither an expression nor a function. A check constraint contains an expression and may contain a function as part of its definition.

Here is another example:

CREATE TABLE Pupils1
(
	StudentNumber nchar(10) not null primary key,
	FullName nvarchar(50),
	Gender nvarchar(32) CHECK(Gender IN (N'Male', N'Female', N'Unknown'))
);
GO
INSERT Pupils1
VALUES(N'927-947-08', N'Frank Judah', N'Male');
GO
INSERT Pupils1
VALUES(N'297-394-03', N'Ernesto Djedje', N'Not Available');
GO

 


Home Copyright © 2007-2011 FunctionX.com, Inc.