Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamically discovering row relationships Expand / Collapse
Author
Message
Posted Friday, January 18, 2013 11:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:37 PM
Points: 2,391, Visits: 17,929
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
Post #1409043
Posted Tuesday, January 22, 2013 9:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:25 PM
Points: 1,191, Visits: 9,880
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.
Post #1410101
Posted Tuesday, January 22, 2013 11:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:37 PM
Points: 2,391, Visits: 17,929
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
Post #1410176
Posted Wednesday, February 13, 2013 6:57 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
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.
Post #1419793
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse