将exisitng int列更改为sybase中的标识 [英] Alter exisitng int column to identity in sybase

问题描述

Sybase 12.5

Sybase 12.5

我在生产中已有一个表，需要对其进行更改以自动填充其PK int列-创建表时，理想情况下将ID列创建为Identity.该ID列是其他多个表中的外键，因此不能删除表并重新开始.问题是，我无法将PK设置为IDENTITY，并且使用当前值创建一个temp列并将其复制到新的IDENTITY列也失败.

I have an existing table in production that needs it's PK int column to be altered such that it is auto populated - when the table was created it would ideally have had the ID column created as an Identity. This ID column is a foreign key in multiple other tables so deleting the table and starting again isn't an option. Problem is, I can't set the PK as an IDENTITY, and creating a temp column with the current values and copying these to a new IDENTITY column is also failing.

由于已经填充了ID列，所以我不能简单地将此列转换为IDENTITY(至少，我还没有找到可以做到这一点的SQL).

As the ID column is already populated, I can't simply turn this column into an IDENTITY (at least, I haven't found any SQL that will do this).

我已经创建了该表的副本，其中的另一列保存了当前的PK值(division_id_tmp).

I have created a copy of the table, with an additional column holding the current PK value (division_id_tmp).

CREATE TABLE division_tmp
(
   division_id int IDENTITY NOT NULL 
   division_id_tmp int,      
   description varchar(255) NOT NULL,
   is_active tinyint DEFAULT 1 NOT NULL,
)

当我尝试从原始表中输入数据脚本时

When i try to enter a datascript from the original table

INSERT INTO division_tmp
(description,is_active,division_id_tmp,division_id) 
VALUES 
('TEST',1,36,34)

我收到以下错误:

Error: Explicit value specified for identity field in table 'division_tmp' 
when 'SET IDENTITY_UPDATE' is OFF.

如果我运行以下语句:

SET IDENTITY_INSERT division_tmp OFF

该语句执行时没有任何抱怨，但是尝试插入数据脚本结果导致与上述相同的错误.如果我运行该语句

The statement executes without complaint, but attempts to insert the datascript result in the same error as above. If I run the statement

SET IDENTITY_INSERT ac_division_lookup_awd ON

我得到了错误

Error: Unable to 'SET IDENTITY_INSERT' for table '**division_tmp**' because 
IDENTITY_INSERT or IDENTITY_UPDATE is already ON for the table '**division**' in 
database 'preserve'.

我不能成为第一个遇到此问题的人?关于我要去哪儿有什么想法吗?

I can't be the first person who's experienced this problem? Any ideas on where I'm going wrong?

谢谢

推荐答案

已解决.认为根本问题是我是一种工具.

Solved it. Think the underlying issue is I am a tool.

之间感到困惑:

IDENTITY_INSERT 

和

IDENTITY_UPDATE

所以，我需要做的是:

alter table division add division_id_tmp int IDENTITY not null 
SET IDENTITY_UPDATE division ON
update division set division_id_tmp = division_id
SET IDENTITY_UPDATE division OFF
alter table division drop division_id
EXEC sp_rename 
    @objname = 'division.division_id_tmp', 
    @newname = 'division_id', 
    @objtype = 'COLUMN'

工作完成！

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