How can I replace the existing primary key with a new primary key on my table?

I'm working with a legacy SQL Server database which has a core table with a bad primary key.

The开发者_JAVA技巧 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) INT column.

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:

1. Adding the new Id column to the table and making it nullable
2. Filling it with unique integers and making it NOT NULL
3. Dropping the existing primary key while ensuring there's a uniqueness constraint still on that column
4. Setting the new Id column to be the new primary key and identity

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

1. Add a new column with an auto incrementing integer in the base table, ensure it has a unique index on it (to be replaced later by the primary key)
2. For each foreign key relationship pointing to the base table add a new column in the child table. (note this can result in adding more than one column in the child table if more than one relationship)
3. For each instance of a key in the child table enter a value into the new foreign key field(s)
4. Replace your foreign key relationships such that the new column now serves
5. Make the new column in the base table the primary
6. Drop the old primary key in the base table and each old foreign key in the children.

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