Add Column with a check on acceptable range of values

Here is the code to add a new column to an existing table with a check on range. The following column only allows values of 0,1 or 2.

IF NOT EXISTS (SELECT FROM dbo.sysobjects s
INNER JOIN dbo.syscolumns c ON =
WHERE = object_id(N'[dbo].[H2O_ResultMap]') AND = 'ResultTypeFlag')
ALTER TABLE dbo.H2O_ResultMap ADD ResultTypeFlag INT NOT NULL
, CONSTRAINT df_ResultMap_ResultTypeFlag DEFAULT 0 FOR ResultTypeFlag
, CONSTRAINT chk_ResultMap_ResultTypeRange CHECK (ResultTypeFlag >=0 AND ResultTypeFlag <=2)


About Shashi Kuppa

I have 14 years of Information System experience. My background ranges from software architecture, analysis, design and development to team leadership and project management. My primary strengths include .Net system architecture, object oriented development using C#, web development using and SQL server development, reporting and integration. I am an excellent team player, good communicator and have a proven track record for goal-oriented project leadership and management
This entry was posted in Development and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s