tell me the relationship from parent table to child table

Posted by Madhuri123 under Sql Server on 7/16/2011 | Points: 10 | Views : 3095 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts