I'm working with a legacy SQL Server database which has a core table with a bad primary key.
The key is of type
NVARCHAR(50) and contains an application-generated string based on various things in the table. For obvious reasons, I'd like to replace this key with an auto-incrementing (identity)
This is a huge database and we're upgrading it piece-by-piece. We want to minimize the changes to tables that other components write to. I figured I could change the table without breaking anything by just:
Item 3 is proving very painful. Because this is a core table, there are a lot of other tables with foreign key constraints on it. To drop the existing primary key, it seems I have to delete all these foreign key constraints and create them again afterwards.
Is there an easier way to do this or will I just have to script everything?
Afraid that is the bad news. We just got through a big project of doing the same type of thing, although our head DBA had a few tricks up his sleeve. You might look at something like this to get your scripts generated for the flipping of the switch:
I once did the same thing and basically used the process you describe. Except of course you have to first visit each other table and add new foreign key pointing to the new column in your base table
So the approach I used was
It is doable and not as hard as it might sound at first. The crux is a series of update statements for the children table of the nature
Update child_table set new_column = (select new_primary from base) where old_primary = old_foreign