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
)
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!!

Saturday, May 01, 2004

Got a GMail account today ...

Yesterday I created a GMail a/c (vmvadivel@gmail.com) for myself. Thought would inform you all about that.

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

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 !!