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.
e.g.
tblUser (
oldID varchar(10),
newID int, -- The new column added, I'm assuming at this point you have the column populated
...
)
tblUserLogin (
keyUser_oldID varchar(10),
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.
UPDATE tblUserLogin
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!