Execute the following Microsoft T-SQL example script in SSMS Query Editor to add a new check constraint to the Person table WITH CHECK in the tempdb database:

USE tempdb;

GO

-- SQL create table with select into

SELECT TOP 10 ContactID, FirstName, LastName, Email=EmailAddress

INTO Person

FROM AdventureWorks.Person.Contact

GO

-- SQL check constraint - SQL alter table

-- SQL with check - check existing data for constraint

ALTER TABLE [dbo].[Person]

WITH CHECK

ADD CONSTRAINT [EmailCheck] CHECK

(

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

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

      AND CHARINDEX('@',LTRIM([Email])) > 2

)

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 1

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

/* Partial result

ContactID   FirstName   LastName    Email

8           Carla       Adams       carla0@adventure-works.com

2           Catherine   Abel        catherine0@adventure-works.com

12          Celine      Dion        celinedion@sympatico.ca

6           Frances     Adams       frances0@adventure-works.com

1           Gustavo     Achong      gustavo0@adventure-works.com

*/

-- Cleanup

DROP TABLE Person

GO