如何更改 SQL Azure 上的主键

发布于 2024-11-01 16:38:52 字数 1031 浏览 3 评论 0原文

我将更改 SQL Azure 上的主键。但在使用 Microsoft SQL Server Management Studio 生成脚本时会引发错误。因为SQL Azure上的每个表都必须包含一个主键。而且我不能在创建之前删除它。如果我必须改变它,我该怎么办?

脚本生成的

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.

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?

Script generated

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

Error message

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

对不⑦ 2024-11-08 16:38:52

我遇到了这个问题,并在论坛上联系了 Azure 团队。基本上是不可能的。您需要创建一个新表并将数据传输到其中。

我所做的是创建一个事务,并在其中执行以下操作:

  • 将旧表重命名为 OLD_MyTable。

  • 使用正确的主键创建新表并将其命名为 MyTable。

  • 从 OLD_MyTable 中选择内容
    进入 MyTable。

  • 删除 OLD_MyTable。

您可能还需要对任何约束调用 sp_rename,以免它们发生冲突。

另请参阅:http://social.msdn.microsoft.com /Forums/en/ssdsgetstarted/thread/5cc4b302-fa42-4c62-956a-bbf79dbbd040

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:

  • Renamed the old table to OLD_MyTable.

  • Create the new table with the correct Primary Key and call it MyTable.

  • Select the contents from OLD_MyTable
    into MyTable.

  • Drop OLD_MyTable.

You may also need to call sp_rename on any constraints so they don't conflict.

See also: http://social.msdn.microsoft.com/Forums/en/ssdsgetstarted/thread/5cc4b302-fa42-4c62-956a-bbf79dbbd040

看透却不说透 2024-11-08 16:38:52

升级SQL V12并支持堆。因此,您可以删除主键并重新创建它。

upgrade SQL V12 and heaps are supported on it. So you can drop the primary key and recreate it.

时光磨忆 2024-11-08 16:38:52

我知道这对你自己来说可能有点晚了,但它可能对其他人有帮助。

我最近遇到了这个问题,发现最简单的解决方案是从 Azure 下载数据库,在本地还原它,在本地更新主键(因为键约束是 SQL Azure 特定问题),然后将数据库还原回 Azure 。

这避免了与重命名数据库或在数据库之间传输数据有关的任何问题。

I appreciate that this may be late in the day for yourself, but it may help others.

I recently came across this issue and found the least painful solution was to download the database from Azure, restore it locally, update the primary key locally (as the key constraint is a SQL Azure specific issue), and then restore the database back into Azure.

This saved any issues in regards to renaming databases or transferring data between them.

骑趴 2024-11-08 16:38:52

您可以尝试以下脚本。更改它以适合您的表定义。

EXECUTE sp_rename N'[PK_MyTable]', N'[PK_MyTable_old]',  'OBJECT'

CREATE TABLE [dbo].[Temp_MyTable](
[id] [int] NOT NULL,
[text] [text] NOT NULL CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED (
[id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON))

INSERT INTO dbo.[Temp_MyTable] (Id, Text)
SELECT Id, Text FROM dbo.MyTable

drop table dbo.MyTable
EXECUTE sp_rename N'Temp_MyTable', N'MyTable', 'OBJECT'

You can try the following scripts. Change it to suit for your table def.

EXECUTE sp_rename N'[PK_MyTable]', N'[PK_MyTable_old]',  'OBJECT'

CREATE TABLE [dbo].[Temp_MyTable](
[id] [int] NOT NULL,
[text] [text] NOT NULL CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED (
[id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON))

INSERT INTO dbo.[Temp_MyTable] (Id, Text)
SELECT Id, Text FROM dbo.MyTable

drop table dbo.MyTable
EXECUTE sp_rename N'Temp_MyTable', N'MyTable', 'OBJECT'
空城缀染半城烟沙 2024-11-08 16:38:52

这个问题已经过时了,因为最新版本的 SQL Azure 已经支持更改 PK。而且您不必创建临时表。

This question is outdated because changing PK is already supported in latest version of SQL Azure. And you don't have to create temporary table.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文