SqlServer 使用 MySql 创建表，如 auto_increment 主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/85034/
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
SqlServer create table with MySql like auto_increment primary key
提问by Aldur
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 中创建一个表，该表将在插入时添加一个自动递增的主键。这应该是一个类似于 MySql auto_increment 功能的自动增加的 id。（以下）
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?
有没有办法在不创建插入触发器的情况下做到这一点？
回答by SQLMenace
Like this
像这样
create table foo
(
user_id int not null identity,
name varchar(50)
)
回答by Amy B
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)
)
回答by HLGEM
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。使用 scope_identity() 来获取这个值。我知道这有点偏离主题，但它对于您了解如何在 SQL Server 中使用标识很重要。相信我，您不想解决相关记录的问题，这些记录具有错误的身份值。这可能会在数月内悄悄地出错，然后才被发现，之后几乎不可能修复数据。
回答by Troels Arvin
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 的 IDENTITY 和 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 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 allows you to update values in an AUTO_INCREMENT column.
MSSQL refuses to update values in an IDENTITY column.
- MySQL 要求为 AUTO_INCREMENT 列定义唯一约束（通常以主键的形式）。
MSSQL 没有这样的要求。 - MySQL 允许您手动将值插入到 AUTO_INCREMENT 列中。
MSSQL 可防止您手动将值插入 IDENTITY 列；如果需要，您可以通过在插入之前发出“SET IDENTITY_INSERT tablename ON”命令来覆盖它。 - MySQL 允许您更新 AUTO_INCREMENT 列中的值。
MSSQL 拒绝更新 IDENTITY 列中的值。
回答by Darren Kopp
Just set the field as an identity field.
只需将该字段设置为身份字段即可。
回答by Manu
declare the field to be identity
将字段声明为身份
回答by Nasir
As advised above, use an IDENTITY field.
如上所述，请使用 IDENTITY 字段。
CREATE TABLE foo
(
user_id int IDENTITY(1,1) NOT NULL,
name varchar(50)
)
回答by CodeRot
As others have said, just set the Identity option.
正如其他人所说，只需设置身份选项。
