I want to modify an existing primary key on a SQL Azure table.
It currently has one column, and I want to add another.
Now, on SQL Server 2008 this was a piece of cake, just did it in SSMS, poof. Done.
This is how the PK looks like if I script it from SQL Server:
ALTER TABLE [dbo].[Friend] ADD CONSTRAINT [PK_Friend] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[Id] ASC
)
However, on SQL Azure, when I try to execute the above, it will of course fail:
Table 'Friend' already has a primary key defined on it.
Fine, so I try to drop the key:
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.
Ok, so I try to create a temporary clustered index in order to drop the PK:
CREATE CLUSTERED INDEX IX_Test ON [Friend] ([UserId],[Id])
Which results in:
Cannot create more than one clustered index on table 'Friend'. Drop the existing clustered index 'PK_Friend' before creating another.
Great, a catch22 moment.
How do I add the UserId column to my existing PK?
Best Answer
Note: as of Azure SQL Database v12, these restrictions no longer apply.
The is no such thing as a 'primary index'. There is such a thing as a 'primary key' and also there is such a thing as a 'clustered index'. Distinct concepts, often confused. With this distinction in mind, lets revisit the question:
Q1) Can the clustered index in a SQL Azure table be modified?
A: Yes. Use
WITH (DROP_EXISTING=ON)
:Q2) Can the clustered index of a table that has a primary key constraint be modified?
A: Yes, same as above, as long as the primary key constraint is not enforced via the clustered index:
Q3) Can the primary key constraint of a table be modified?
A: Yes, as long as the primary constraint is not enforced via the clustered index:
Q4) Can the primary key of a table be modified when is enforced via the clustered index?
A: Yes, if the table never had any rows:
Q5) Can the primary key of a table be modified when is enforced via the clustered index if the table is populated?
A: No. Any operation that converts a populated clustered index into a heap will be blocked in SQL Azure, even if the table is empty:
As a side note: the constraint can be modified if the table is truncated.
The workaround to change the PK constraint of a populated table is to do the good old
sp_rename
trick:The
sp_rename
approach has some issues, most importantly being that permissions on the table do not carry over during the rename, as well as foreign key constraints.