SQL Server Change Primary Key Data Type

Question

I am working on SQL Server 2012:

I have a table with a primary key column as INT. I need to change this to a GUID.

Do I alter the table and remove int column as primary key?

Add the GUID column and set it as Primary and drop the old INT column?

Thank you.

Answer 1

You can't change primary key column,unless you drop it..Any operations to change its data type will lead to below error..

The object 'XXXX' is dependent on column 'XXXX'.

Only option is to

1.Drop primary key
2.change data type
3.recreate primary key

ALTER TABLE t1   DROP CONSTRAINT PK__t1__3213E83F88CF144D;    GO    alter table t1  alter column id varchar(10) not null  alter table t1 add primary key (id) 

From 2012,there is a clause called (DROP_EXISTING = ON) which makes things simple ,by dropping the clustered index at final stage and also keeping old index available for all operations..But in your case,this clause won't work..

So i recommend

1.create new table with desired schema and indexes,with different name
2.insert data from old table to new table
3.finally at the time of switch ,insert data that got accumulated
4.Rename the table to old table name

This way you might have less downtime

Answer 2

You can change the date type of the primary key in three steps

Step 1 :- Drop the constraint associated with the Primary key

ALTER TABLE table_name  DROP CONSTRAINT constraint_name; 

Step 2 :- Alter the Primay key column to a valid primary key data type

ALTER TABLE  table_name ALTER COLUMN pk_column_name target_data_type(size) not null; 

Step 3 :- Make the altered column primary key again

ALTER TABLE table_name ADD PRIMARY KEY (pk_column_name); 

---

PS :-

• You can get the Constraint name from the error message when you try to alter the pk_column

• If you already have data in the pk_column make sure the source and target data type of the column both can be used for the existing data. else another two steps would be needed to move the existing data to a temporary column and then perform the steps and bring back that data after vetting and dropping that temporary column.

---

Answer 3

Below is a script I wrote to help us deploy a change to primary key column data type.

This script assumes there aren't any non-primary key constraints (e.g. foreign keys) depending on this column.

It has a few safety checks as this was designed to be deployed to different servers (dev, uat, live) without creating side effects if the table was somehow different on a server.

I hope this helps someone. Please let me know if you find anything wrong before down-voting. I'm more than happy to update the script.

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WITH (NOLOCK) WHERE C.TABLE_CATALOG = '<<DB>>' AND C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME = '<<Table>>' 
AND C.COLUMN_NAME = '<<COLUMN>>' AND C.DATA_TYPE = 'int') -- <- Additional test to check the current datatype so this won't make unnecessary or wrong updates
BEGIN
    DECLARE @pkName VARCHAR(200);
    SELECT @pkName = pkRef.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkRef WITH (NOLOCK)
    WHERE pkRef.TABLE_CATALOG = '<<DB>>' AND pkRef.TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '<<Table>>'

    IF(@pkName IS NOT NULL)
    BEGIN
        -- Make sure the primary key name is the one you are going to use in script beyond this point.
        IF(@pkName != '<<PRIMARY KEY NAME>>') 
        BEGIN
            RAISERROR ('Unexpected primary key name - The primary key found has a different name than expected. Please update the script.', 16, 1);
            RETURN;
        END

        ALTER TABLE dbo.<<Table>>
              DROP CONSTRAINT <<PRIMARY KEY NAME>>; -- Note: this is not a string or a variable (just type the PK name)
        SELECT 'Dropped existing primary key';
    END

    ALTER TABLE dbo.<<Table>> ALTER COLUMN ID BIGINT
    SELECT 'Updated column type to big int';

    ALTER TABLE dbo.<<Table>>
          ADD CONSTRAINT <<PRIMARY KEY NAME>> PRIMARY KEY CLUSTERED (<<COLUMN>>);
    SELECT 'Created the primary key';
END
ELSE
BEGIN
    SELECT 'No change required.';
END

Answer 4

In case other tables reference your PK with indexed FK's, these are the steps you must follow.

In this example, the main table's called Main, the single referencing table Reference. I'm changing the datatype to NVARCHAR(7). To use it:

• Find/replace all these table names with your own;
• Modify the data type;
• You might also need to separately find/replace the dbo schema;
• I'm using syntax which includes constraint names - if you want, also update these to your preferred naming conventions.

ALTER TABLE dbo.Main ADD IdNew NVARCHAR(7);
UPDATE dbo.Main SET IdNew = Id;

-- For all tables with FK's to this Main:

ALTER TABLE dbo.Reference ADD MainIdNew NVARCHAR(7);
UPDATE dbo.Reference SET MainIdNew = MainId;

ALTER TABLE dbo.Reference DROP CONSTRAINT FK_Reference_MainId_Main_Id;
DROP INDEX IX_Reference_MainId ON dbo.Reference;
ALTER TABLE dbo.Reference DROP COLUMN MainId;

-- Until here

ALTER TABLE dbo.Main DROP CONSTRAINT PK_Main;
ALTER TABLE dbo.Main DROP COLUMN Id;

EXEC sp_rename 'dbo.Main.IdNew', 'Id', 'COLUMN';
ALTER TABLE dbo.Main ALTER COLUMN Id NVARCHAR(7) NOT NULL;
ALTER TABLE dbo.Main ADD CONSTRAINT PK_Main PRIMARY KEY (Id);

-- Again for all tables with FK's to this Main:

EXEC sp_rename 'dbo.Reference.MainIdNew', 'MainId', 'COLUMN';
ALTER TABLE dbo.Reference ADD CONSTRAINT FK_Reference_MainId_Main_Id FOREIGN KEY (MainId) REFERENCES dbo.Main(Id);
CREATE INDEX IX_Reference_MainId ON dbo.Reference(MainId);