Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Related: stackoverflow.com/questions/5645145/… –  Nick Chammas Oct 18 '11 at 21:43
add comment

1 Answer

up vote 20 down vote accepted

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):

create table Friend (
    UserId int not null,
    Id int not null);
go  
create clustered index cdxFriend on Friend (UserId, Id);
go
create clustered index cdxFriend on Friend (Id, UserId) with (drop_existing=on);
go

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:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key nonclustered (Id));
create clustered index cdxFriend on Friend (UserId, Id);
go
create clustered index cdxFriend on Friend (Id, UserId) with (drop_existing=on);
go

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:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key nonclustered (Id));
go
create clustered index cdxFriend on Friend (UserId, Id);
go
alter table Friend drop constraint pk_Friend;
alter table Friend add constraint pk_Friend primary key nonclustered (UserId)
go

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:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key clustered (UserId, Id));
go
alter table Friend drop constraint pk_Friend;
alter table Friend add constraint pk_Friend primary key clustered (Id, UserId)
go

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:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key clustered (UserId, Id));
go
insert into Friend (UserId) values (1);
delete from Friend;
go
alter table Friend drop constraint pk_Friend;

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:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key clustered (UserId, Id));
go
insert into Friend (UserId) values (1);
go

create table FriendNew (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend_New primary key clustered (Id, UserId));
go

set identity_insert FriendNew on;
insert into FriendNew (UserId, Id) 
select UserId, Id
from Friend;
set identity_insert FriendNew off;
go

begin transaction
exec sp_rename 'Friend', 'FriendOld';
exec sp_rename 'FriendNew', 'Friend';
commit;
go

sp_help 'Friend';

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.

share|improve this answer
    
A1-A4 are No answers in my case. A5 did the trick, although my Id isn't an identity column. –  Magnus Oct 19 '11 at 4:27
    
sp_rename workaround was helpful! –  Justin Dec 5 '12 at 2:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.