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 |
|
Tringo
Starting Member
1 Post |
 Posted - 2007-08-31 : 08:13:06
|
| Hi,For many years i was a developer managing DBT teams but have now moved to the lofty heights of a solution architect and was recently asked to provide an estimate for the work required to effectively re-key a database.Essentially we have a relational database where we are looking to retire the exising configuration data and replace with a standardised configuration which we will roll out across the whole project. The new configuration will be semantically identical (i.e. the same configuration values) however the primary keys will change since from a technical perspective it is in a 'different' order in the configuration tables.Now this leaves us with a problem in that the FK's from the transactional data will be pointing to the wrong values in the configuration table. So in affect replacing the old config with the new breaks the referential integrity and therefore the meaning of the data will change. To overcome this i proposed (and its not rocket science) that you would do a comparison of the old and new config and setup a series of mapping / cross reference tables with the old and new FK values. Then with the mapping tables in place you would update all the transactional data with the new FK's which point to the correct configuration items.However the DBT team have come back with a horrendous estimate for the work. from what i have outline above does that seem a sensible way to realign and re-establish referential integrity without doing a full ETL?!Any advice / opinions greatfully recieved. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-08-31 : 09:51:58
|
if you are certain that the new data is self-consistent, seems like you can just bulk load all the data with all fks disabled, then enable all the keys. have a look at bcp.exe.
elsasoft.org |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-01 : 05:03:05
|
| We have "changed" from using an IDENTITY for a given relationship to using a GUID instead. We added the GUID column, changed the FKs etc., and then added a Trigger that checked that the ID and GUID matched on all the relationships. This caught some bugs in the software where we had failed to make changes to reference by the GUIDs instead of the IDs. We still haven't, and that's about 2 years later! But at least I am confident that the GUIDs and IDs are in-step in all the relationships they participate in.Dunno if that is relevant in your circumstances?Kristen |
|
|
|
|
|
|
Re-establishing Referential Integrity