You do not have to make another table if you are changing the primary key.
The steps I would carry out are:
- Add the new integer column that will become the primary key.
- Populate it with unique values and make sure you have a unique constraint on it. (It must be unique to be certain it can become primary key)
- In each child table add an integer column for the foreign key.
- Populate the new foreign key by looking up the old foreign key.
newID int, -- The new column added, I'm assuming at this point you have the column populated
keyNewID int NULL,
Run the following... I'm work mostly with SQL Server these days, so hopefully this pseudocode gives the right idea. The principle applies to any major table-based database system.
SET keyNewID = (SELECT newID FROM tblUser WHERE oldID = keyUser_oldID
- Add the foreign key between the two new columns.
- Make sure that the new primary key is set to auto increment.
- Once oldID and keyUser_oldID are no longer referenced anywhere you can drop those columns. But only after you are completely certain!
A few things to consider
That's the general principle, a few things to consider.
- This works in simple cases and assumes you can stop people using the database for a while and that any code that uses these tables is ready to be switched to use the new column names.
- Subqueries are not the most efficient way, but this is a one-off task and unless you have millions of rows and limited time to make the switch it should be fine.
- You didn't say what you are storing in the varchar field? If it is a number (but in varchar format) you could always do UPDATE tblSomeTable SET keyNewID = CAST(keyOldID as int) which would be faster and easier.
Most importantly though...
..you don't mention if the database is used for anything important. Even if it isn't:
- Back it up beforehand if you haven't already. If you make a mistake in an update statement you can always reference a backup copy.
- Think the steps through, these are structural changes, 30 minutes spent planning could save hours of time later on.
- Have a backup plan in case something goes wrong!