Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
Dear All

How to delete forigen key reference table rows.

table 1:

id, name (heare Id is primary key)

table 2:

Emp_ID, dept, ID (here id is forigen key reference)
Posted
Comments
Varun Sareen 1-Mar-12 3:24am    
Where are you stuck dear?

you can easily delete from table 2 but if you want to delete from table 1
use
delete from table1 cascade
 
Share this answer
 
Comments
Photon_ 1-Mar-12 5:13am    
thanks
If you want to delete from table2. either


1. You should to delete all the corresponding entries from table1, then only u can delete from table2,Or else it'll show
'The DELETE statement conflicted with the REFERENCE constraint'

2. 1 more option is there if u drop the constraint of foreign key from table1, then also u can delete rows from table 2..


DELETE FROM table1 WHERE Id=1
GO
DELETE FROM table2 WHERE Emp_ID=1



1 more method -->Or Else use ON DELETE CASCADE with table Definition.
 
Share this answer
 
v2
Hi there....

just try this one

ALTER TABLE table2
ADD CONSTRAINT FK_table2ID FOREIGN KEY (ID)
REFERENCES table1(id)
ON DELETE CASCADE



best of luck....
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900