How to delete data in child tables?

Question

I have 6 tables named A,B,C,D,E,F

I have A's Primary key, Now B have ref in A, C has ref in B, D has ref in C

E has ref in A, F has ref in B

Now I want to delete A's records and its related records in other tables and I have only A's primary key,

Best way to delete all records.

Thanks in advance.

It might be safer to add a boolean field name isActive to A, and set it to 0. — Dan Bracuk, Apr 26 '13 at 11:42
I have think over this, actually it's good but I want to delete records physically too. — Karan Gandhi, Apr 26 '13 at 11:47

score 6 · Accepted Answer · edited May 23 '17 at 11:58

6

One of

Use a stored procedure to delete child tables, using WHERE EXISTS
Set foreign keys to CASCADE DELETE, so all child rows are deleted when you DELETE from A
See How do I use cascade delete with SQL Server?

edited May 23 '17 at 11:58

Community

answered Apr 26 '13 at 11:39

gbn

Thank you very much, I think Cascade delete has performance issues. – Karan Gandhi Apr 26 '13 at 11:46

score 1 · Answer 2 · answered Apr 26 '13 at 11:39

1

Delete the rows in the child tables by SELECTing their foreign key equal to A's primary key, then delete from A using the primary key.

answered Apr 26 '13 at 11:39

duffymo

score 0 · Answer 3 · answered Apr 26 '13 at 11:39

0

If its SQL Server you can set cascade delete to remove related records.

answered Apr 26 '13 at 11:39

Andrew

Thank you very much, I think Cascade delete has performance issues. – Karan Gandhi Apr 26 '13 at 11:47

3 Answers3