SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Need to delete records from dependent tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chennaraaj
Starting Member

India
17 Posts

Posted - 07/30/2014 :  05:27:54  Show Profile  Reply with Quote

Hi All,

Need to delete records from dependent tables based on foreign key relationship.

ex.

Table1 foreign key with Table2
Table2 foreign key with Table3
Table3 foreign key with Table4

ect.....

from the above tables i need to delete the records from Table2,3,4 based on Table1 ID




rk

gbritton
Aged Yak Warrior

857 Posts

Posted - 07/30/2014 :  08:15:19  Show Profile  Reply with Quote
(at least your tables don't have circular references!)

If you think of your tables in tiers or levels, level 0 would be a table with no FKs. Level 1 would have one or more FKs refering to Level 0 tables, etc. (I know that the real world is not always that simple!) Find Level "n" -- the highest tier in the dependency tree. No tables point to it via FKs. You start deleting from that table, then move to Level n-1, n-2 ... Level 0.

NOte that SQL can do this for you if you have cascading set up. See here: http://technet.microsoft.com/en-us/library/ms186973(v=sql.105).aspx

Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 07/30/2014 :  08:19:32  Show Profile  Reply with Quote

CREATE TABLE T1
(ID1 INT Primary Key)


CREATE TABLE T2
(ID2 INT Primary Key,ID1 INT)

CREATE TABLE T3
(ID3 INT Primary Key,ID2 INT)


CREATE TABLE T4
(ID4 INT Primary Key,ID3 INT)


ALTER TABLE t4
ADD CONSTRAINT FK_T4 FOREIGN KEY (ID3)
REFERENCES T3 (ID3)
-- ON DELETE CASCADE
ALTER TABLE t3
ADD CONSTRAINT FK_T3 FOREIGN KEY (ID2)
REFERENCES T2 (ID2)
-- ON DELETE CASCADE
ALTER TABLE t2
ADD CONSTRAINT FK_T2 FOREIGN KEY (ID1)
REFERENCES T1 (ID1)
-- ON DELETE CASCADE


INSERT INTO T1
VALUES(1)
INSERT INTO T2
VALUES(2,1)
INSERT INTO T3
VALUES(3,2)
INSERT INTO T4
VALUES(4,3)

SELECT * FROM T1
SELECT * FROM T2
SELECT * FROM T3
SELECT * FROM T4

DELETE FROM t4
WHERE ID3 = 3

DELETE FROM t3
WHERE ID2 = 2

DELETE FROM t2
WHERE ID1 = 1

DELETE FROM t1
WHERE ID1 =1

SELECT * FROM T1
SELECT * FROM T2
SELECT * FROM T3
SELECT * FROM T4



ALTER TABLE T4 DROP CONSTRAINT FK_T4
ALTER TABLE T3 DROP CONSTRAINT FK_T3
ALTER TABLE T2 DROP CONSTRAINT FK_T2



ALTER TABLE t4
ADD CONSTRAINT FK_T4 FOREIGN KEY (ID3)
REFERENCES T3 (ID3)
ON DELETE CASCADE
ALTER TABLE t3
ADD CONSTRAINT FK_T3 FOREIGN KEY (ID2)
REFERENCES T2 (ID2)
ON DELETE CASCADE
ALTER TABLE t2
ADD CONSTRAINT FK_T2 FOREIGN KEY (ID1)
REFERENCES T1 (ID1)
ON DELETE CASCADE


DELETE FROM T1
WHERE ID1 = 1

SELECT * FROM T1
SELECT * FROM T2
SELECT * FROM T3
SELECT * FROM T4
Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 07/30/2014 :  08:21:29  Show Profile  Reply with Quote
Oh - copied over my blurb with my code: You can either delete in the appropriate order from lowest child to parent or alter your constraints to CACADE Delete; Both option I coded here, but it looks like gbritton is pretty much saying the same thing.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000