Sql-server – Rename Primary Key constraint on Azure

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.

The distinction is mainly historical. The relational model was developed in part in response to the way IMS handled data.

The presently released version of IMS provides the user with a choice for each file: a choice between no indexing at all (the hierarchic sequential organization) or indexing on the primary key only . . .

Source: A Relational Model of Data for Large Shared Data Banks

Codd retained that term. (Same source.)

Normally, one domain (or combination of domains) of a given relation has values which uniquely identify each element (n-tuple) of that relation. Such a domain (or combination) is called a primary key.

It's not unusual for a relation to have more than one "combination of domains" that uniquely identify each tuple. The relational model offers no theoretical basis for elevating the status of one of them to "primary". As far as the relational model is concerned, all candidate keys are created equal.

As far as standard SQL is concerned, any column or combination of columns that's declared UNIQUE can be the target of a foreign key constraint. In the past, dbms platforms differed slightly in how they handled NULL in a column declared UNIQUE. That's part of the reason old-school database designers lean toward NOT NULL UNIQUE declarations; the other part is that NOT NULL UNIQUE is functionally equivalent to PRIMARY KEY.

There is a semantic difference between a UNIQUE index and a UNIQUE constraint. I prefer to see constraints expressed as constraints.