tell me the relationship from parent table to child table

Posted by Madhuri123 under Sql Server on 7/16/2011 | Points: 10 | Views : 2153 | Status : [Member] | Replies : 1
Hi,
In My Project I have two tables one Category and another one is subcategory,

for example In Category table i have JOBS and in Subcategory Freelancers,IT JobsHealthcare - Medicin, and the structure of the tables is
my Category Table
Category_id int Unchecked
Categoryname varchar(50) Checked
Status bit Checked

SubCategory Table

subcategoryid int Unchecked
subcategoryname varchar(50) Checked
Category_id int Checked
Status bit Checked

now i am deleting the category it is deleting , if i deleting this category , should not be deleted and it must show some messg like u should not deleted the category
for this what should i give relationship between parent class and child class


Thanks in Advance
T.Madhuri




Responses

Posted by: PandianS on: 7/16/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Please follow the following scenario...
/*Parent Table */

CREATE TABLE Category
(
Category_id INT IDENTITY(1,1) NOT NULL Constraint PK_Category_id PRIMARY KEY,
Categoryname VARCHAR(50) NULL,
[Status] BIT NULL
)
Go

/*Child Table*/
CREATE TABLE SubCategory
(
subcategoryid INT IDENTITY(1,1) NOT NULL,
subcategoryname VARCHAR(50) NULL,
Category_id INT NULL FOREIGN KEY REFERENCES Category(Category_id),
[Status] BIT NULL
)
GO

/*Inserting Two Categories*/
INSERT Category(Categoryname,[Status]) VALUES('Jobs',1)
INSERT Category(Categoryname,[Status]) VALUES('Books',1)
Go

/*Inserting Two Sub-Categories under the "Jobs" category*/
INSERT SubCategory(subcategoryname,Category_id,[Status]) VALUES('Freelancers',1,1)
INSERT SubCategory(subcategoryname,Category_id,[Status]) VALUES('IT JobsHealthcare - Medicin',1,1)
Go

/*Trying to Delete the "Category" which is referred by the "Subcategory" table*/
Begin Try
Delete Category Where Categoryname ='Jobs'
End Try
Begin Catch
If Error_Number()=547
Raiserror('You can not delete this category as It has been referred by "SubCategory"',16,1)
End Catch

/*So, the User defined Error would be as follows...*/
You can not delete this category as It has been referred by "SubCategory"


I hope, It would help you :)


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Madhuri123, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response