Changing primary key from varchar to int

Question

I have a user table where I am use varchar as primary key. Now from a different place I got the performance tip that int is better than varchar for primary key.

So I would like to change my primary key to int. For that I have created another user table and copied the data to that table except the previous primary key value.

Up to this there is no problem.

Now my problem is that I have many tables which are related to the user table through the previous primary key. Now I want to relate the rows of the other tables through to new primary key value.

How to do this?

Answer 1

You do not have to make another table if you are changing the primary key.

The steps I would carry out are:

1. Add the new integer column that will become the primary key.
2. 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)
3. In each child table add an integer column for the foreign key.
4. 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

1. Add the foreign key between the two new columns.
2. Make sure that the new primary key is set to auto increment.
3. 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!