how to use primary key and related foreign key

Posted by Vanchi050585 under Sql Server on 8/30/2010 | Points: 10 | Views : 4323 | Status : [Member] | Replies : 2
Anyone,

Pls tell me. i dont know how to set foreign key related to primary key of main table. if i delete the record of main table , in case sub table having any records related to this, the record should not delete , the message will be displayed.

pls send the example.

Thanks

Thanks
tvn



Responses

Posted by: Shankul2784 on: 8/30/2010 [Member] Bronze | Points: 25

Up
0
Down
Hi,

In stored procedure while deleting the record you can check the details records & then delete the master records or there is cascade delete option is available.

Thanks & Regards,

Shailesh

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

Posted by: PandianS on: 8/30/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

1.Creating one Master/Parent table:
CREATE TABLE Tb_Table1

(
ID INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(10)
)
Go
2. Inserting records into Master table
INSERT Tb_Table1(Column1) VALUES('Sample1')

INSERT Tb_Table1(Column1) VALUES('Sample2')
Go
3.Creating Detail/Child table which refering the Master table(Tb_Table1)
CREATE TABLE Tb_Table2

(
ID INT CONSTRAINT FK_Tb_Table1_ID FOREIGN KEY REFERENCES Tb_Table1(ID),
Column2 VARCHAR(10)
)
Go
4.Inserting records into Child table(Tb_Table2) refering the Tb_Table1
INSERT Tb_Table2(ID,Column2) VALUES(2,'Sample2')

Go
5. The actual records available in Parent and Child Tables
SELECT * FROM Tb_Table1

SELECT * FROM Tb_Table2
Go

ID Column1
1 Sample1
2 Sample2

ID Column1
2 Sample2
6. Deleting one record from Tb_Table1(Parent Table)
But, The record has been refered by Child table (Tb_Table2),
DELETE Tb_Table1 WHERE ID = 2

Error Message:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Tb_Table1_ID". The conflict occurred in database "RM_Phase3", table "dbo.Tb_Table2", column 'ID'.
The statement has been terminated.
Because, The record ID = 2 has been refered by the Child table(Tb_Table2), So the Conflict error occurred

7.Deleting record from Tb_Table1(Parent Table), which is not refered by the Child table(Tb_Table2)
DELETE Tb_Table1 WHERE ID = 1
Now, You can delete the Record ID = 1, Because The record ID=1 is available only in Master Table(TB_Table1), but not refered by the Child table(Tb_Table2)

Result is:
(1 row(s) affected)

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Login to post response