SqlServer创建带有MySql的表，如auto_increment主键。

I want to make a table in SqlServer that will add, on insert, a auto incremented primary key. This should be an autoincremented id similar to MySql auto_increment functionality. (Below)

我想在SqlServer中创建一个表，它将在insert时添加一个自动递增的主键。这应该是一个自动递增的id，类似于MySql auto_increment功能。(下图)

create table foo  
(  
user_id int not null auto_increment,  
name varchar(50)  
)

Is there a way of doing this with out creating an insert trigger?

是否有一种方法可以通过创建插入触发器来实现这一点?

8 个解决方案

#1

Like this

像这样

create table foo  
(  
user_id int not null identity,  
name varchar(50)  
)

#2

OP requested an auto incremented primary key. The IDENTITY keyword does not, by itself, make a column be the primary key.

OP请求自动递增主键。IDENTITY关键字本身并不使列成为主键。

CREATE TABLE user
(
  TheKey int IDENTITY(1,1) PRIMARY KEY,
  Name varchar(50)
)

#3

They have answered your question but I want to add one bit of advice for someone new to using identity columns. There are times when you have to return the value of the identity just inserted so that you can insert into a related table. Many sources will tell you to use @@identity to get this value. Under no circumstances should you ever use @@identity if you want to mantain data integrity. It will give the identity created in a trigger if one of them is added to insert to another table. Since you cannot guarantee the value of @@identity will always be correct, it is best to never use @@identity. Use scope_identity() to get this value instead. I know this is slightly off topic, but it is important to your understanding of how to use identity with SQL Server. And trust me, you did not want to be fixing a problem of the related records having the wrong identity value fed to them. This is something that can quietly go wrong for months before it is dicovered and is almost impossible to fix the data afterward.

他们已经回答了你的问题，但是我想给新的使用identity列的人添加一点建议。有时，您必须返回刚才插入的标识的值，以便插入到相关的表中。许多来源会告诉您使用@@identity来获取此值。如果您想保持数据完整性，在任何情况下都不应该使用@@identity。如果将触发器中的一个添加到另一个表中，它将提供在触发器中创建的标识。既然不能保证@@identity的值总是正确的，最好永远不要使用@@identity。使用scope_identity()获取这个值。我知道这有点偏离主题，但是这对于您理解如何使用SQL Server标识非常重要。相信我，你并不想解决相关记录的问题，因为这些记录提供了错误的身份值。这是一种可以悄无声息地出错几个月的东西，在它被覆盖之前，几乎不可能在事后修复数据。

#4

As others have mentioned: add the IDENTITY attribute to the column, and make it a primary key.

正如其他人提到的:将IDENTITY属性添加到列中，并将其作为主键。

There are, however, differences between MSSQL's IDENTITY and MySQL's AUTO_INCREMENT:

但是，MSSQL的标识和MySQL的AUTO_INCREMENT之间存在差异:

MySQL requires that a unique constraint (often in the form of a primary key) be defined for the AUTO_INCREMENT column.
MSSQL doesn't have such a requirement.
MySQL要求为AUTO_INCREMENT列定义一个惟一的约束(通常以主键的形式)。MSSQL没有这样的要求。
MySQL lets you manually insert values into an AUTO_INCREMENT column.
MSSQL prevents you from manually inserting a value into an IDENTITY column; if needed, you can override this by issuing a "SET IDENTITY_INSERT tablename ON" command before the insert.
MySQL允许您手动将值插入到AUTO_INCREMENT列中。MSSQL阻止您手动将值插入标识列;如果需要，您可以在插入之前发出“SET IDENTITY_INSERT tablename”命令来覆盖这一点。
MySQL allows you to update values in an AUTO_INCREMENT column.
MSSQL refuses to update values in an IDENTITY column.
MySQL允许您在AUTO_INCREMENT列中更新值。MSSQL拒绝更新标识列中的值。

#5

Just set the field as an identity field.

只需将字段设置为标识字段。

#6

declare the field to be identity

将字段声明为标识

#7

As advised above, use an IDENTITY field.

如上所述，使用标识字段。

CREATE TABLE foo
(
user_id int IDENTITY(1,1) NOT NULL,
name varchar(50)
)

#8

As others have said, just set the Identity option.

正如其他人所说，只是设置身份选项。

#1