如何在SQL Server数据库中添加auto_increment主键? [英] How do I add a auto_increment primary key in SQL Server database?

问题描述

我设置了一个当前没有主键的表.我需要做的就是添加primary key, no null, 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.

我正在使用Microsoft SQL Server数据库.我知道这不可能用一个命令完成，但是我尝试的每个命令都会不断返回语法错误.

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.

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

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

我已经创建了主键，甚至将其设置为非null.但是，我无法设置auto_increment.

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

我尝试过:

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

我正在使用NVARCHAR，因为它不允许我将NOT NULL设置为int

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

推荐答案

可以在单个命令中完成.您需要为自动编号"设置IDENTITY属性:

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)

请参见MSDN上的 ALTER TABLE和IDENTITY

这篇关于如何在SQL Server数据库中添加auto_increment主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！