Need to delete records from dependent tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

Need to delete records from dependent tables

Author

Topic

chennaraaj
Starting Member

17 Posts

Posted - 2014-07-30 : 05:27:54

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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-30 : 08:15:19

(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

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-30 : 08:19:32

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

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-30 : 08:21:29

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources