How to alter a primary key in an SQL Server 2005 database without losing any data?

Question

I have one table in my database that has a primary key and its data type is varchar(10) but it stores only 5 char, so I want to change its data type from varchar(10) to varchar(5) without losing any data.

Currently I'm creating a temp table, moving data from the original table to the temp table, then altering it, and lastly restoring the data from temp to the main table again.

I want to know if there is another solution for this problem?

Answer 1

I think that you should be creating a table which has a identity int primary key and use your varchar field as a unique alternative natural key for your database.

I know it means that you have to alter any other foreign keys pointing there, but if you are already doing the work you describe, I would go the full hog.

IMHO

Answer 2

I had to this for db2 database. These were the steps suggested by the DBA:

1. Drop dependent objects such as views or procs...Pls. keep a copy of ddl before doing drops
2. Rename existing table to may be _old
3. Create table with correct structure.
4. Perform runstats on _old table
5. Load data from _old table to this new table. Load using identity override flag if you want to preserve identity columns
6. Recreate view and procs
7. Rebind all dependent procs.

You can omit step 4 & 7 if it is db2 specific. Additionally,if there is no prob in SQLServer you can omit dropping and re-creating dependent objects.

Answer 3

The general idea is laid out below. Dont forget to back up your DB first.

ALTER TABLE tableName ADD tempColumn VARCHAR(5)

UPDATE tableName SET tempColumn = pkColumn

-- Do this for all constraints

ALTER TABLE tableName DROP CONSTRAINT pkConstraint

ALTER TABLE tableName DROP COLUMN pkColumn

ALTER TABLE tableName ADD pkColumn VARCHAR(5)

UPDATE tableName SET pkColumn = tempColumn

ALTER TABLE tableName DROP COLUMN tempColumn

Answer 4

In most DBMS, running a simple ALTER TABLE statement would do the whole job in one go, not losing any data. Are you sure the MS SQL Server doesn't support that? (If not, maybe you need a better DBMS - but I don't think that'll be the problem.)

Having seen a multi-step operation outlined, I'm puzzled. In the DBMS I use mainly, all that would be necessary is:

ALTER TABLE WhatEver MODIFY pkcolumn VARCHAR(5) NOT NULL;

It could even be an 'in place alter'; it would appear to complete almost immediately, even though the actual change would take place over a long time as the actual rows were altered. (OK; actually for a VARCHAR field that might not occur; for a CHAR field - which would be a decent option when the length is only 5 or 10 bytes - it would be an 'in place alter'.)

Answer 5

Since you specify that you're using SQL Server, that change can and should be done using the design tools.

Start the Management Studio program and connect to the server. Then open up the database in question and find the table.

Right-click on the table and select Design, and then in that view, do the changes.

You now have two choices:

1. You can click on the Save button, and MS SQL Server will script and execute the necessary changes for you
2. or, you can use the Generate Change Script button. In my IDE, this button is the leftmost button on the same toolbar as the key/constraint buttons (not the same toolbar that has the Save button)

If you use the latter function, you will see the script code in a dialog, and can optionally save it to disk. This allows you to get an overview of what will happen should you want to execute it.

The problem with changing the primary key is that all references from other tables in to to that primary key needs to be dropped first, and recreated afterwards, and the SQL Server Management Studio program is smart enough to generate (at least in 99% of the cases) all the SQL you need, in the right order.