Ufdjrw

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.

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

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.

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

Right in the table you want to change the PK type >> Modify. Go in the column, change the type and save. If you want to see the code for such a change, before saving, you can right-click >> "Generate Change Script ..".