SQL Server snippets

How to add constraints to existing table

Let suppose you have the following #Students table and you want that Period column can only assume values in 1, 2, 3 or 4.

CREATE TABLE #Students(
[Name] varchar(50),
[Surname] varchar(50),
[Period] int,
[Sport] int,
[History] int,
[English] int,
[Geography] int)

The way to add a constraint is the following

ALTER TABLE #Students
ADD CONSTRAINT chk_period CHECK (Period IN (1,2,3,4))

At this point, if we try to insert 5 in as Period we'll get an error

INSERT INTO
#Students([Name],[Surname],[Period],[Sport],[History],[English],[Geography])
VALUES
('Mary','Brown',1,17,15,NULL,30)
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the CHECK constraint "chk_period". The conflict occurred in database "tempdb", table "dbo.#Students___________________________________________________________________________________________________________000000000049", column 'Period'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

while if we insert 1 as Period it will be correctly executed

INSERT INTO
#Students([Name],[Surname],[Period],[Sport],[History],[English],[Geography])
VALUES
('Mary','Brown',1,17,15,NULL,30)
SQL Server snippet constraint

You can find an interactive version of this example following this link .

Back to SQL Server cookbook page