UDF's in Constraints ...

In this post I have explained the way to use UDF (User Defined Functions) in constraints. For the purpose of discussion I have provided the structure of 2 (self explanatory) tables "MasterTable" and "ChildTable". Sample data for MasterTable have also been provided below.

--Table structure of MasterTable:
Create Table MasterTable
(
ItemID int Identity Primary Key,
ItemName Varchar(50),
Status bit
)

--Sample data for the table MasterTable:
Insert Into MasterTable (ItemName, Status) Values ('Rice',1)
Insert Into MasterTable (ItemName, Status) Values ('Wheat',1)
Insert Into MasterTable (ItemName, Status) Values ('Corn flakes',0)

--Table structure of ChildTable:
Create Table ChildTable
(
ItemID int Foreign Key References MasterTable(ItemID),
Quantity int
)
Go

/*
Let us now create an user defined function to check whether the Item exist and its status is 1. According to this sample Status 1 means the record is enabled if it is 0 it is disabled.
*/

Create Function CheckItemID
(
@ItemValue int
)
Returns Integer
As
Begin
Return
(
Select Count(*)
From
dbo.MasterTable
Where
ItemID = @ItemValue and
Status = 1
)
End
Go

/*
Add a constraint that calls the User defined function "CheckItemID" for validating the ItemID before entering the record into ChildTable.
*/

Alter Table ChildTable
Add
Constraint CK_ChildTable_CheckItemID Check ( dbo.CheckItemID( ItemID ) = 1 )
GO

Let us now test the above code snippet

--Insert valid ItemID whose status is 1
Insert into ChildTable (ItemID, Quantity) Values (1,10)

--Insert valid ItemID whose status is 0
Insert into ChildTable (ItemID, Quantity) Values (3,10)
-- This record would not insert and it would throw an error.

--Insert an ItemID which is not there in MasterTable
Insert into ChildTable (ItemID, Quantity) Values (25,10)

Hope this code snippet helps you understand UDF usage within Constraints!!