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 c.name FROM dbo.sysobjects s
INNER JOIN dbo.syscolumns c ON s.id = c.id
WHERE s.id = object_id(N'[dbo].[H2O_ResultMap]') AND c.name = 'ResultTypeFlag')
BEGIN
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)
END
GO

Advertisements

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 Asp.net 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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s