如何在SQL Server数据库中添加auto_increment主键?

I have a table set up that currently has no primary key. All I need to do is add a primary key, no null, auto_increment.

我设置了一个当前没有主键的表。我只需要添加一个主键，没有null, auto_increment。

I'm working with a Microsoft SQL Server database. I understand that it can't be done in a single command but every command I try keeps returning syntax errors.

我正在使用Microsoft SQL Server数据库。我理解它不可能在一个命令中完成，但是我尝试的每个命令都会返回语法错误。

edit ---------------

编辑- - - - - - - - - - - - - - - - - -

I have created the primary key and even set it as not null. However, I can't set up the auto_increment.

我已经创建了主键，甚至将它设置为not null。但是，我不能设置auto_increment。

I've tried:

我试过了:

ALTER TABLE tableName MODIFY id NVARCHAR(20) auto_increment
ALTER TABLE tableName ALTER COLUMN id NVARCHAR(20) auto_increment
ALTER TABLE tableName MODIFY id NVARCHAR(20) auto_increment
ALTER TABLE tableName ALTER COLUMN id NVARCHAR(20) auto_increment

I'm using NVARCHAR because it wouldn't let me set NOT NULL under int

我使用NVARCHAR，因为它不会让我在int下设为NOT NULL

6 个解决方案

#1

106

It can be done in a single command. You need to set the IDENTITY property for "auto number":

它可以在一个命令中完成。您需要为“自动编号”设置标识属性:

ALTER TABLE MyTable ADD mytableID int NOT NULL IDENTITY (1,1) PRIMARY KEY

More precisely, to set a named table level constraint:

更准确地说，设置一个已命名的表级约束:

ALTER TABLE MyTable
   ADD MytableID int NOT NULL IDENTITY (1,1),
   CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (MyTableID)

See ALTER TABLE and IDENTITY on MSDN

请参阅MSDN上的ALTER TABLE和IDENTITY

#2

If the table already contains data and you want to change one of the columns to identity:

如果该表已经包含数据，而您想将其中一列更改为identity:

First create a new table that has the same columns and specify the primary key-kolumn:

首先创建一个具有相同列的新表，并指定主键kolumn:

create table TempTable
(
    Id int not null identity(1, 1) primary key
    --, Other columns...
)

Then copy all rows from the original table to the new table using a standard insert-statement.

然后使用标准插入语句将原始表中的所有行复制到新表。

Then drop the original table.

然后删除原始表。

And finally rename TempTable to whatever you want using sp_rename:

最后，使用sp_rename将TempTable重命名为任意值:

http://msdn.microsoft.com/en-us/library/ms188351.aspx

#3

You can also perform this action via SQL Server Management Studio.

您还可以通过SQL Server Management Studio执行此操作。

Right click on your selected table -> Modify

右键单击选定的表——>修改

Right click on the field you want to set as PK --> Set Primary Key

右键单击要设置为PK的字段——>设置主键

Under Column Properties set "Identity Specification" to Yes, then specify the starting value and increment value.

在列属性中，将“标识规范”设置为Yes，然后指定起始值和增量值。

Then in the future if you want to be able to just script this kind of thing out you can right click on the table you just modified and select

以后，如果你想把这些东西写下来你可以右键点击你刚刚修改过的表格然后选择

"SCRIPT TABLE AS" --> CREATE TO

“脚本表AS”——>创建TO

so that you can see for yourself the correct syntax to perform this action.

这样你就能看到执行这个动作的正确语法。

#4

If you have the column it's very easy.

如果你有这一列，这很简单。

Using the designer, you could set the column as an identity (1,1): right click on the table → design → in part left (right click) → properties → in identity columns, select #column.

使用设计师,您可以设置列作为一个身份(1,1):右键单击表→设计→部分左(右击)→属性→标识列,选择#列。

Properties:

属性:

Identity column:

标识列:

#5

In SQL Server 2008:

在SQL Server 2008:

Right click on table
右键单击表
Go to design
去设计
Select numeric datatype
选择数值数据类型
Add Name to the new column
向新列添加名称
Make Identity Specification to 'YES'
将身份说明设置为“是”

#6

-2

you can try this... ALTER TABLE Your_Table ADD table_ID int NOT NULL PRIMARY KEY auto_increment;

你可以试试这个…修改表Your_Table添加表_id int而不是NULL主键auto_increment;

#1

106