how to use primary key and related foreign key

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

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