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

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.

How can I alter an existing Primary Key on SQL Azure?
A very effective set piece can be conflict with the spirit world.  A nice example of the kind of thing I am thinking of is from the 1982 Conan film, where the wizard opens a gateway to the spirit world to get demons to revive Conan, and where Valeria fights incorporeal demons to save Conan (YouTube, from 6:45 into the clip to the end &amp; onto next clip).

RQ3 had nice mechanics for dealing with these situations, with shamans who can take characters to the other side through their fetch, spirit combat to handle attacks by hostile spirits, using magic points as an orthogonal measure of strength to hit points.  

AD&amp;D was pretty lousy for this kind of thing back in the day: its distinction between combat involving the incorporeal attribute or in the ethereal and astral realms didn't really work for setting up situations.  But newer D&amp;Ds seem to have better resources, though I've not seen this in action.

What good game mechanics are there for handling conflicts with spirits?

Postscript

The kind of thing game mechanics need, I think, to sustain interest in spirits and spirit combat are:


Spirits are immaterial and that means you have to do different sorts of things to influence them.  E.g., if the best way to deal with an ancient ghost is to chop it up with an axe, that'll spil that atmosphere a bit;
Likewise, spirits are potentially powerful adversaries.  Not very high-powered spells that can get rid of most spirits with a 65% chance of success detract from interest;
Characters can be experts in spirits, and it is good if the nature of those experts draws on resonant real-world and fictional atmospheric devices such as shamans, ancestor worship, fetches, and the like.  It's also good if characters regularly need the services of these figures. 

