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