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,
--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),
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
ItemID = @ItemValue and
Status = 1
Add a constraint that calls the User defined function "CheckItemID" for validating the ItemID before entering the record into ChildTable.
Alter Table ChildTable
Constraint CK_ChildTable_CheckItemID Check ( dbo.CheckItemID( ItemID ) = 1 )
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!!