Search code examples
t-sqlsql-server-2012primary-keyalter-tablechangetype

T-SQL: When is dropping and recreating tables needed when changing the primary key type?


I have the following tables:

CREATE TABLE B( Id  [uniqueidentifier] PRIMARY KEY NOT NULL,    [Name] varchar(100) NULL;
CREATE TABLE A (Id  [uniqueidentifier] PRIMARY KEY NOT NULL,    BId [uniqueidentifier] NOT NULL CONSTRAINT FK_A_B   FOREIGN KEY(BId)    REFERENCES dbo.B(Id));

This is a simplified example, actually there are other columns in the tables that are irrelevant to the question. I need to change both tables' primary key columns to be of type int and to auto-increment BUT to also preserve the old Id values as GUID columns My question is: Which is the best way to do these changes? Two ways come to mind - the first is to drop all indexes on B.Id, add a new identity column to B table, do the same for A.Id, then add a new null column to A (BId_New INT), update A and then make BId_New not null. The other way is to save the tables' data to copied tables, truncate the A and B tables, then alter their schema and insert the values from the copied tables to the actual ones. Which way is better and under what circumstances?


Solution

    1. Create the new Identity key on B table, you can define it as Not Null to start with since the identity attribute will populate it.
    2. Remove the FK constraint on table A
    3. Remove the PK constraint on table B. You may also need to drop the clustered index if you want your new PK to be a clustered index as well. I'm not sure if removing the PK constraint will also remove the clustered index.
    4. Create a new PK constraint on the identity attribute
    5. Create the new int attribute in the A table. DO NOT use identity.
    6. Update the new int attribute by joining A & B on the GUID and retrieving the new B primary key
    7. Alter the new column in table A to change it to Not Null
    8. Create the FK constraint to link table A to table B on the new int key