Create constraints on a column in SQL Server

Hello Friends

Today i was asssigned some work to to put some missing constraints on tables .Adding a constraint sometimes gets very complex so i thought that i should share it with you all how i implemeted it in my scenarios:

(1) Add constraint for sprecial character validation

CREATE TABLE [testtable][ID] [int] NULL,  [Projectname] [nvarchar](200)  ,[pkey] [varchar](200) )

now i want to make sue that Projectname column should no contain any special character apart from ‘_’.

To add a constraint expression for it follow the steps :

(a)Expand testtable tree.

(b)Right click on Constrant folder and select New Constraint option .A new window will appear .

(c) Paste the following expression in the expression area (shown in the newly opened window)

          ([ProjectName] IS NULL OR [ProjectName] like ‘[a-zA-Z_][a-zA-Z0-9_]%’)

(d) Close the window and you are done with it .

(2)Add Constraint for making  column ProjectName Unique

ALTER TABLE testtable WITH CHECK  ADD CONSTRAINT chk_UniqueProjectName UNIQUE ([ProjectName])

For further information click here

Advertisements

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