Início / dba / Perguntas / 6937
Accepted
Magnus
Asked: 2011-10-19 10:21:54 +0800 CST

Como posso alterar uma chave primária existente no SQL Azure?

  • 772

Desejo modificar uma chave primária existente em uma tabela do SQL Azure.
Atualmente, tem uma coluna e desejo adicionar outra.

Agora, no SQL Server 2008 isso foi moleza, só fiz no SSMS, puf. Feito. É assim que o PK se parece se eu fizer o script do SQL Server:

ALTER TABLE [dbo].[Friend] ADD  CONSTRAINT [PK_Friend] PRIMARY KEY CLUSTERED 
(
  [UserId] ASC,
  [Id] ASC
)

No entanto, no SQL Azure, quando tento executar o acima, é claro que falhará:

Table 'Friend' already has a primary key defined on it.

Tudo bem, então eu tento largar a chave:

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

Ok, então tento criar um índice clusterizado temporário para descartar o PK:

CREATE CLUSTERED INDEX IX_Test ON [Friend] ([UserId],[Id])

O que resulta em: Cannot create more than one clustered index on table 'Friend'. Drop the existing clustered index 'PK_Friend' before creating another.

Ótimo, um momento catch22.

Como adiciono a coluna UserId ao meu PK existente?

primary-key azure-sql-database

1 respostas

  1. Best Answer

    Observação: a partir do Banco de Dados SQL do Azure v12, essas restrições não se aplicam mais.

    O não existe como um 'índice primário'. Existe uma 'chave primária' e também existe um 'índice agrupado'. Conceitos distintos, muitas vezes confundidos. Com esta distinção em mente, vamos revisitar a questão:

    Q1) O índice clusterizado em uma tabela do SQL Azure pode ser modificado?
    R: Sim. Usar 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) O índice clusterizado de uma tabela que possui uma restrição de chave primária pode ser modificado?
    R: Sim, como acima, desde que a restrição de chave primária não seja aplicada por meio do índice clusterizado:

    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) A restrição de chave primária de uma tabela pode ser modificada?
    R: Sim, desde que a restrição primária não seja aplicada por meio do índice clusterizado:

    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) A chave primária de uma tabela pode ser modificada quando aplicada por meio do índice clusterizado?
    R: Sim, se a tabela nunca teve linhas:

    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) A chave primária de uma tabela pode ser modificada quando aplicada por meio do índice clusterizado se a tabela for preenchida?
    R: Não. Qualquer operação que converta um índice clusterizado preenchido em um heap será bloqueada no SQL Azure, mesmo que a tabela esteja vazia :

    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;

    Como observação: a restrição pode ser modificada se a tabela for truncada .

    A solução alternativa para alterar a restrição PK de uma tabela preenchida é fazer o bom e velho sp_renametruque:

    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';

    A sp_renameabordagem tem alguns problemas, sendo o mais importante que as permissões na tabela não são transferidas durante a renomeação, bem como restrições de chave estrangeira.

    • 34

relate perguntas