SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically discovering row relationships


Dynamically discovering row relationships

Author
Message
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4363 Visits: 18732
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
HowardW
HowardW
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6253 Visits: 9892
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.
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4363 Visits: 18732
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
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25564 Visits: 12467
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search