We can call user defined function in a constraint. This is very helpful when we have to add similar kind of constraint on many tables.
Let’s take an example here
Create a function
CRAETE FUNCTION [TestFucntion] (@Salary nvarchar(50))
DECLARE @Exists BIT
SET @Exists =0
SET @Exists =1
Execute the above script and it will create a function which can be seen in object explorer of the selected database, under the function folder.
Create a table employee
Create table Employee (ID int, Salary money)
Add a testConstraint on table employee
Alter TABLE Employee
WITH NOCHECK ADD CONSTRAINT [testConstraint]
The above script will add constraint on the table Employee and will not allow entering salary more than 1500.
Thus we can do any other activity here in function and can write complex constraint for table easily.
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