更改主键和标识的表列 [英] Alter table column for primary key and identity

问题描述

我创建了表并想更改该表.我想添加一个主键和identity(1,1).

I have table created and want to alter that table. I want to add a primary key and identity(1,1).

我可以应用主键，但应用身份会出错.有什么遗漏吗?

I can apply primary key but applying identity gives error. Is anything missing?

ALTER TABLE MyTable ADD PRIMARY KEY (Id)

如何使用主键添加身份?

How can I add identity as well with primary key?

推荐答案

你不能改变数据库中现有列的定义，添加IDENTITY属性(也不删除它).您必须使用 IDENTITY 属性创建一个 new 列:

You cannot alter the definition of an existing column in the database, to add the IDENTITY property (nor to remove it). You have to create a new column with the IDENTITY property:

ALTER TABLE MyTable ADD NewID int IDENTITY(1,1) not null

遗憾的是，您无法将旧 ID 值分配给这个新列.如果你想分配 ID 值，然后让 IDENTITY 接管，你最好用你想要的结构创建一个新表，然后从旧表中导入数据(你可以使用IDENTITY_INSERT 为 IDENTITY 列).

Unfortunately, you're not then able to assign the old ID values to this new column. If you want to assign the ID values, and then let IDENTITY take over, you'd be better off creating a new table with the structure you want, then importing data from the old table (you can use IDENTITY_INSERT to assign values to the IDENTITY column).

如果需要，您可以删除旧表并重命名新表.

You would then drop the old table and rename the new table, if required.

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