Thursday, May 27, 2004

Sunday, May 23, 2004

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

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
Select Count(*)
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!!

Saturday, May 01, 2004

Got a GMail account today ...

Yesterday I created a GMail a/c ( for myself. Thought would inform you all about that.

If you also have an active blogger a/c ( you could see the invite for creating a Gmail account immediately after logging into the site (

If at all you don't have an blogger account check the heading "Interested in an account" here. I guess that would help you to get a GMail id at some later date!!

I was actually wondering if Google gives 1 GB of space to each user how big their servers needs to be?!??! Won't they run into storage problem within few months after they release it for public?? At that time I got to see the post from my good old friend Pandurang Nayak which had some interesting facts about GMail !!