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', 'celinedion@sympaticoca')

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', 'celinedion@sympatico.ca')

GO

-- (1 row(s) affected)

SELECT * FROM Person ORDER BY Email

GO

/*

ContactID   FirstName   LastName    Email

8           Carla Adams             carla0@adventure-works.com

2           Catherine   Abel        catherine0@adventure-works.com

12          Celine      Dion        celinedion@sympatico.ca */

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

DROP TABLE tempdb.dbo.Person

DROP FUNCTION dbo.ufnEmailCheck

GO

Related articles:

MSDN Library: CHECK Constraints

SELECT INTO