SQL Server 2008：ALTER TABLE 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7361076/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008: ALTER TABLE query
提问by Ram
I am getting error for the below code
我收到以下代码的错误
ALTER TABLE ADM_Roles ALTER COLUMN RoleID int IDENTITY (1, 1)
Incorrect syntax near the keyword IDENTITY.
关键字附近的语法不正确IDENTITY。
回答by marc_s
You cannotchange an existing column into an IDENTITYcolumn - you will need to add a new columnthat has the identity flag:
您不能将现有列更改为IDENTITY列 - 您需要添加具有标识标志的新列：
ALTER TABLE dbo.ADM_Roles
ADD NewRoleID INT IDENTITY (1, 1)
If you need to, you can later on drop the old column and rename the new column to the old name:
如果需要，您可以稍后删除旧列并将新列重命名为旧名称：
ALTER TABLE dbo.ADM_Roles DROP COLUMN RoleID
EXEC sp_rename @objName = 'dbo.ADM_Roles.NewRoleID',
@newName = 'RoleID',
@objType = 'COLUMN'
回答by MiBu
From the MSDN
来自 MSDN
You can't alter the existing columns for identity.
You have 2 options,
Create a new table with identity & drop the existing table
Create a new column with identity & drop the existing column But take special care when these columns have any constraints / relations.
你有2个选择，
创建具有标识的新表并删除现有表
创建一个具有标识的新列并删除现有列但是当这些列有任何约束/关系时要特别小心。
Example approach:
示例方法：
- In this approach you can't retain the existing data values on the newly created identity column;
The identity column will hold the sequence of number
Alter Table Names Add Id_new Int Identity(1,1) Go Alter Table Names Drop Column ID Go Exec sp_rename 'Names.Id_new', 'ID','Column'
- 在这种方法中，您不能在新创建的标识列上保留现有数据值；
标识列将保存数字序列
Alter Table Names Add Id_new Int Identity(1,1) Go Alter Table Names Drop Column ID Go Exec sp_rename 'Names.Id_new', 'ID','Column'
回答by Friedrich Alegre
Tamnbien puede hacerlo a traves del diagrama de base de datos, modificas las propiedades de la tabla y agregar la columna que deseas que sea "identity" en la columna "identity column"
Tamnbien puede hacerlo a traves del diagrama de base de datos, modificas las propiedades de la tabla y agregar la columna que deseas que sea "identity" en la columna "identity column"
Eso me arreglo el problema de agregar identity a una columna existente.
Eso me arreglo el problema de agregar identity a una columnaexistente。
回答by JAiro
you have to remove the word "int".
你必须删除“int”这个词。
ALTER TABLE ADM_Roles ALTER COLUMN RoleId IDENTITY (1, 1);
