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.
| Author |
Topic |
|
VentureFree
Starting Member
19 Posts |
 Posted - 2009-12-09 : 08:33:43
|
| I have a normalized database comprised of about 7 different tables with mostly many-to-one relationships. I recently imported a bunch of data from an older database resulting in some redundancy. As a result I need to go in and delete some of the data.My question is basically this: Assuming I know the ID of the specific record I want to delete, is there any reason why I shouldn't just delete that row from the main table and then find and delete rows in the other tables which are not referenced from the main table any longer (they are many-to-one, so may be referenced by more than one row on the main table)? This would allow me to delete a complete record without having to know precisely which rows in the other tables need to be deleted too. Or is there some compelling reason to determine which rows exactly need to be deleted from the other tables?I hope this question was clear enough.Things should be made as simple as possible, but not any simpler - Albert Einstein |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-12-09 : 10:15:59
|
| Sounds like you model has no referential integrity. Ideally sql server wouldn't allow you to first delete the parent row if any children exist because you have FOREIGN KEYs established. assuming you are starting with a parent ID, you would first go to all child tables and either Inspect the rows that are about to be deleted (WHERE <parentid> = ?) and/or simply DELETE them using the same WHERE clause. When all children rows have been deleted you can then delete the parent.Be One with the OptimizerTG |
 |
|
|
|
|
|
Deleting from Normalized Database