更改表集标识列 [英] Alter table set Identity column

问题描述

我有一个包含以下列的 sybase 表(测试"):

I have a sybase table ("Test") having the following columns:

MyIdentity numberic(9,0)
Name
User

桌子上有很多记录.我想将 MyIdentity 列更改为身份.

The table is filled with lots of records. I want to alter MyIdentity column to be an identity.

表中当前没有 MyIdentity 的重复值.如何更改表格并将 MyIdentity 设置为身份?

There are currently no duplicate values for MyIdentity in the table. How can i alter the table and set MyIdentity as identity?

推荐答案

至少在我的版本 (Adaptive Server Enterprise/15.0.3) 上，以下似乎可以解决问题(省略了语法糖):

At least on my version (Adaptive Server Enterprise/15.0.3) the following seems to do the trick (syntactic sugar omitted):

添加新的标识列:

alter table Test add newMyIdentity numeric(9,0) identity not null

准备更新:

set identity_insert Test on
set identity_update Test on

复制现有 ID(以便能够保留外键等):

Copy the existing ID (to be able to preserve foreign keys etc):

update Test set newMyIdentity=MyIdentity

删除旧定义，重命名新创建的列:

Drop the old definition, rename the newly created column:

alter table Test drop MyIdentity
sp_rename "Test.newMyIdentity", MyIdentity

清理:

set identity_insert Test off
set identity_update Test off
sp_recompile Test

这篇关于更改表集标识列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！