主页 / dba / 问题 / 6937
Accepted
Magnus
Asked: 2011-10-19 10:21:54 +0800 CST

如何更改 SQL Azure 上的现有主键？

  • 772

我想修改 SQL Azure 表上的现有主键。
它目前有一列，我想添加另一列。

现在，在 SQL Server 2008 上，这是小菜一碟，只是在 SSMS 中做到了，噗。完毕。如果我从 SQL Server 编写脚本，这就是 PK 的样子：

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

但是，在 SQL Azure 上，当我尝试执行上述操作时，它当然会失败：

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

好的，所以我尝试删除密钥：

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

好的，所以我尝试创建一个临时聚集索引以删除 PK：

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

结果是： Cannot create more than one clustered index on table 'Friend'. Drop the existing clustered index 'PK_Friend' before creating another.

太好了，一个 catch22 时刻。

如何将 UserId 列添加到我现有的 PK？

primary-key azure-sql-database

1 个回答

  1. Best Answer

    注意：从 Azure SQL 数据库 v12 开始，这些限制不再适用。

    这不是“主要索引”之类的东西。有“主键”之类的东西，也有“聚集索引”之类的东西。不同的概念，经常混淆。考虑到这种区别，让我们重新审视这个问题：

    Q1) 可以修改 SQL Azure 表中的聚集索引吗？
    答：是的。使用 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) 有主键约束的表的聚集索引可以修改吗？
    A：是的，和上面一样，只要不通过聚集索引强制执行主键约束：

    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) 可以修改表的主键约束吗？
    答：可以，只要不通过聚集索引强制执行主约束：

    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：是的，如果表从未有任何行：

    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) 如果表格被填充，可以通过聚集索引强制修改表格的主键吗？
    答：不可以。任何将填充的聚集索引转换为堆的操作都将在 SQL Azure 中被阻止，即使表为空：

    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;

    附带说明：如果表被截断，则可以修改约束。

    更改填充表的 PK 约束的解决方法是使用旧sp_rename技巧：

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

    该sp_rename方法存在一些问题，最重要的是表的权限在重命名期间不会继承，以及外键约束。

    • 34

相关问题