January 18, 2013 at 11:38 am
My database structure kind of looks like a tree - a main root table with a primary key, tables with foreign keys back to that table, other tables with foreign keys back to the 2nd level, etc. All of my tables (well, for the purpose of this question anyway) are eventually related back to the root through a foreign key relationship. Now, I need to move all the records associated with a specific row in the root table to another database along with all of its associated records down the tree. And I'd like it to be dynamic so that it doesn't have to change as the DB structure is modified. It didn't seem to bad at first, just follow the keys! But it quickly became complex as I realized that some tables might be self-referencing, some have multi-column keys, some tables are optimized for performance and thus have circular key references, and the order of delete (for the cleanup) doesn't necessarily follow the order I discovered the rows in by following the key structure.
So... I've done some searches, but haven't found anyone who has done something similar that I can leverage and make sure I haven't missed anything. Have you seen anyone do something similar (and can point me to it)?
Thanks,
Chad
January 22, 2013 at 9:17 am
Sounds like you're going down the right lines. The correct order of deletes may be unresolvable without disabling/re-enabling constraints, especially if you have circular references spanning multiple tables (urgh).
You may have to test scripting and disabling the constraints, perform the deletes/moves, then re-enable/check constraints (making sure that they can re-enable without error, or rollback the whole transaction). Assuming everything does truly end up linking back to the parent key, the conflicts should resolve themselves when all the deletes have been processed.
January 22, 2013 at 11:09 am
Thanks for the vote of confidence, it's nice to have a second opinion. The theory sounded so nice and clean before I started, but when I actually got in to implement it, it is an amazing mess. I gave up trying to make it completely dynamic and generic, I'm going to have to settle for "will work for everything we have now and the stuff we reasonably expect in the future". That should cut about 6 months off the development. 😉
Thanks Howard,
Chad
February 13, 2013 at 6:57 pm
Try to build a "full select" query to include all sequential references from the "root" table to the smallest "seed":
SELECT *
FROM Table1 A
LEFT JOIN Table2 B ON B.Col = A.Key
LEFT JOIN Table3 C ON C.Col = B.Key
LEFT JOIN Table4 D ON D.Col = C.Key
LEFT JOIN Table2 E ON E.Col = D.Key
WHERE .....
The last row in my example represents a circular reference.
Records returned from the last table in the query are not referenced by anything as keys.
Otherwise you should add another join representing those references.
After you're done with the query replace "SELECT *" with "DELETE E" - must go with no problems.
Continue with "DELETE D", followed by "DELETE C", etc.
By the time you reach "DELETE A" all dependant records should be cleared.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply