DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to build UDF CHECK constraint?

Execute the following script in Microsoft SQL Server Management Studio (SSMS) Query Editor to demonstrate the creation and working of a UDF CHECK constraint:

USE tempdb;

GO

 

-- SQL create table with SELECT INTO

SELECT TOP 10 ContactID, FirstName, LastName,

       Email=convert(varchar(64),EmailAddress)

INTO Person

FROM AdventureWorks.Person.Contact

 

GO

-- Create user-defined function (UDF) for checking

CREATE FUNCTION ufnEmailCheck (@Email varchar(64))

RETURNS BIT

AS

BEGIN

IF EXISTS(SELECT 1 WHERE

      CHARINDEX('.',@Email,CHARINDEX('@',@Email))-CHARINDEX('@',@Email)>1

      AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@Email)))) > 2

      AND CHARINDEX('@',LTRIM(@Email)) > 2) RETURN(1)

RETURN (0)

END

GO

 

 

-- SQL UDF check constraint - SQL alter table

-- SQL with CHECK option - check existing data for constraint

ALTER TABLE [dbo].[Person]

WITH CHECK

ADD CONSTRAINT [EmailCheck] CHECK

(

  dbo.ufnEmailCheck (Email) = 1

)

GO

 

-- SQL check constraint violated missing . (period)

INSERT Person (FirstName, LastName, Email)

VALUES ('Celine', 'Dion', '[email protected]')

GO

 

/*

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the CHECK constraint "EmailCheck".

The conflict occurred in database "tempdb", table "dbo.Person", column 'Email'.

The statement has been terminated.

*/

 

 

-- SQL check constraint met

INSERT Person (FirstName, LastName, Email)

VALUES ('Celine', 'Dion', '[email protected]')

GO

-- (1 row(s) affected)

 

 

SELECT * FROM Person ORDER BY Email

GO

/*

ContactID   FirstName   LastName    Email

8           Carla Adams             [email protected]

2           Catherine   Abel        [email protected]

12          Celine      Dion        [email protected] */

 

------------

DROP TABLE tempdb.dbo.Person

DROP FUNCTION dbo.ufnEmailCheck

GO

 

Related articles:

MSDN Library: CHECK Constraints

SELECT INTO

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE