如何在SQL Azure上更改主键 [英] How can I change primary key on SQL Azure
问题描述
我将更改SQL Azure上的主键.但是使用Microsoft SQL Server Management Studio生成脚本时会引发错误.因为SQL Azure上的每个表都必须包含一个主键.而且我无法在创建之前将其删除.如果必须更改该怎么办?
I am going to change the primary key on SQL Azure. But it throws an error when using Microsoft SQL Server Management Studio to generate the scripts. Because every tables on SQL Azure must contains a primary key. And I can't drop it before create. What can I do if I must change it?
脚本生成
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND name = N'PK_mytable')
ALTER TABLE [dbo].[mytable] DROP CONSTRAINT [PK_mytable]
GO
ALTER TABLE [dbo].[mytable] ADD CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
GO
错误消息
Msg 40054, Level 16, State 2, Line 3
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.
Msg 3727, Level 16, State 0, Line 3
Could not drop constraint. See previous errors.
The statement has been terminated.
Msg 1779, Level 16, State 0, Line 3
Table 't_event_admin' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.
推荐答案
我遇到了这个确切的问题，并在论坛上联系了Azure团队.基本上是不可能的.您需要创建一个新表并将数据传输到其中.
I ran into this exact problem and contacted the Azure team on the forums. Basically it isn't possible. You'll need to create a new table and transfer the data to it.
我所做的是创建一个事务，并在其中执行以下操作:
What I did was create a transaction and within it do the following:
-
将旧表重命名为OLD_MyTable.
Renamed the old table to OLD_MyTable.
使用正确的主键创建新表，并将其命名为MyTable.
Create the new table with the correct Primary Key and call it MyTable.
从OLD_MyTable中选择内容 进入MyTable.
Select the contents from OLD_MyTable into MyTable.
删除OLD_MyTable.
Drop OLD_MyTable.
您可能还需要在任何约束条件下调用sp_rename，以免它们冲突.
You may also need to call sp_rename on any constraints so they don't conflict.
这篇关于如何在SQL Azure上更改主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
