I have a table in SQL Azure database with a clustered primary key index. Today, I wanted to change the primary key of the table. (I know that the primary key should not be changed often, consider that I had to…) .

This was fairly simple with a basic SQL database, however SQL Azure is a completely different proposition.

You cannot add a new table constraint without deleting the existing one AND you cannot have a table without a primary key constraint.

I was left with the only option of creating a new table and copying the data to that table. After doing the copy, I renamed the tables such that the new and the old table names were the same.

This is how I did it….


--ORIGINAL TABLE
CREATE TABLE [dbo].[Employee](
 [Name] [varchar](200) NOT NULL,
 [Id] [int] NOT NULL,
 [Department] [varchar](200) NOT NULL,
 CONSTRAINT [PK_Employee_New] PRIMARY KEY CLUSTERED (
 [Id] ASC,
 [Department] ASC
)WITH ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF))

--NEW TABLE
CREATE TABLE [dbo].[Employee_New](
 [Name] [varchar](200) NOT NULL,
 [Id] [int] NOT NULL,
 [Department] [varchar](200) NOT NULL,
 CONSTRAINT [PK_Employee_New] PRIMARY KEY CLUSTERED (
 [Id] ASC,
)WITH ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF))

--COPY DATA TO NEW TABLE
INSERT INTO [Employee_New]([Name], [Id], [Department])
SELECT [Name], [Id], [Department]
FROM [Employee]

--CHECK ROW COUNTS
select count(*) from [Employee]

select count(*) from [Employee_New]

--RENAME TABLES using SQL Management Studio or following command

EXECUTE sp_rename N'dbo.Employee', N'Employee_Old', 'OBJECT'

EXECUTE sp_rename N'dbo.Employee_New', N'Employee', 'OBJECT'

The other options I had were using a tool to do it for me. Although once known, its not a big task to do manually. But I must admit that if the table were complex and the data too large, I would have had to resort to other measures.

Do you have anything better than what I did? Let me know.

My References

Related articles

Advertisements

Share your thoughts

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s