I have a hopefully quick question that I have not been able to find a simple solution for after lots of searching.
First some quick background info...
I am migrating a database over from SQL 2000 to SQL 2008. The previous application's developers have really made a mess out of the the tables in that the primary keys are all over the place. Some are text, some are integer, some are GUID etc. This mess is further propagated throughout the other tables in the form of foreign keys.
What I would like to accomplish is this, I would like to rename all of these primary keys while migrating to make them more uniform. Problem is that I need these new keys to also update on down to the foreign keys. Is there a way to automatically change primary keys and all corresponding foreign keys automatically during the migration process?
I see the ON UPDATE CASCADE setting but my understanding is that would involve manually changing all foreign key constraints on all referencing tables which would hopefully be unnecessary.
Thanks in advance! I look forward to your responses.
Yes, your question is a quick one. Implementing it on the other hand...
You are doing a fundamental operation on your database. Changing the data model is only the first step. The code of the programs (SP's or embedded) and reports accessing that database will all have to be reviewed.
How many tables are you talking about? You may be faster doing it manually then programming and testing something yourself. I don't know if any off-the-shelf solutions are available.
Do your programs use embedded SQL or do they use Stored Procedures?
Changing your code on such a scale and deep level calls for an elaborate development and test period. I agree on your observations that the current database is not well formed and changing it would make it better. But is this your personal opinion or is it backed strongly by your boss and the users? This will consume a lot of resources, if your database and/or programs/reports are big (many tables, many source lines).
You may have to wait for a major revision of your application before you get the chance to address this problem. In the mean time, you may define some guidelines by witch your colleagues have to abide when they create new tables. You could even restrict their access to your production and test databases, so you are the only one who can create new tables and other objects there. They can still create new tables on the development database and use those during development. But they must discuss those database objects with you, so they are sure you will be willing to create them on the test and production databases, later on in the development phases.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages