如何在 SQL Server 2005 中增加插入语句中的主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13753325/
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
How to increment a primary key in an insert statement in SQL Server 2005
提问by Croeber
I need to write an insertstatement into a table the columns looks like this
我需要在insert表中写一条语句，列看起来像这样
demandtypeid(PK, FK, int, not null)characvalueid(PK, FK, int, not null)percentage(int null)lastuser(varchar(100), null)lastedited(datetime, null)
demandtypeid(PK, FK, int, not null)characvalueid(PK, FK, int, not null)percentage(int null)lastuser(varchar(100), null)lastedited(datetime, null)
Here is the INSERTstatement. Notice the there is not values at the
这是INSERT声明。注意这里没有值
value( , , 'Bob')
as I think that's where the auto-increment command should go
因为我认为这就是自动增量命令应该去的地方
insert into tr_demandtypecharac(demandtypeID, characvalueid, lastuser)
values( , , 'Bob')
Please help with a simple little statement
请帮忙做一个简单的小声明
I just want to know how to manually insert into this table
我只想知道如何手动插入到这个表中
Here's my table structure:
这是我的表结构：
CREATE TABLE [dbo].[tr_demandtypecharac](
[demandtypeid] [int] NOT NULL,
[characvalueid] [int] NOT NULL,
[percentage] [int] NULL,
[lastuser] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lastedited] [datetime] NULL,
CONSTRAINT [PK_tr_dtc_pkey] PRIMARY KEY CLUSTERED
(
[demandtypeid] ASC,
[characvalueid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[tr_demandtypecharac] WITH CHECK
ADD CONSTRAINT [FK_tr_dtc_cvid]
FOREIGN KEY([characvalueid]) REFERENCES [dbo].[tr_characvalue] ([characvalueid])
ALTER TABLE [dbo].[tr_demandtypecharac] WITH CHECK
ADD CONSTRAINT [FK_tr_dtc_dtid]
FOREIGN KEY([demandtypeid]) REFERENCES [dbo].[tr_demandtype] ([demandtypeid])
回答by ean5533
If you want an int column that is unique and autoincrementing, use the IDENTITYkeyword:
如果您想要一个唯一且自动递增的 int 列，请使用IDENTITY关键字：
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)
Then when you insert into the table, do not insert anything for that column -- it will autoincrement itself.
然后，当您插入到表中时，不要为该列插入任何内容——它会自动递增。
回答by Diego
Given the CREATE TABLEstatement you posted, without auto-increment (aka identity) columns, you would insert providing all columns and values, like this:
鉴于CREATE TABLE您发布的语句，没有自动增量（又名身份）列，您将插入提供所有列和值，如下所示：
insert into tr_demandtypecharac(
demandtypeid, characvalueid,
percentage, lastuser, lastedited)
values(2, 3, 80, 'Bob', '01/01/2012')
If, however, you do make them auto-increment by changing the CREATE TABLEto:
但是，如果您确实通过更改为使它们自动递增CREATE TABLE：
CREATE TABLE [dbo].[tr_demandtypecharac](
[demandtypeid] [int] NOT NULL IDENTITY(1,1),
[characvalueid] [int] NOT NULL IDENTITY(1,1),
[percentage] [int] NULL,
[lastuser] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lastedited] [datetime] NULL,
CONSTRAINT [PK_tr_dtc_pkey] PRIMARY KEY CLUSTERED
(
[demandtypeid] ASC,
[characvalueid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
Then you would insert providing all non-identity (non-autoincrement) columns like this:
然后，您将插入提供所有非标识（非自动增量）列，如下所示：
insert into tr_demandtypecharac(
percentage, lastuser,
lastedited)
values(80, 'Bob', '01/01/2012')
However, it is not common to have more than one column as an identity (autoincrement) column, and generally, this column is the only PRIMARY KEY column.
但是，将多个列作为标识（自动增量）列的情况并不常见，并且通常该列是唯一的 PRIMARY KEY 列。
回答by Croeber
If a column is an autoincement column (which is different than a primary key column) then you omit the column in your insert statement and it will be filled in.
如果一列是自动增强列（与主键列不同），那么您可以在插入语句中省略该列，它将被填充。
INSERT INTO tr_demandtypecharac (lastuser) VALUES ('Bob')
回答by Leonardo
You should not use int as primary keys... heres a article about it: http://techtrainedmonkey.com/2012/07/30/why-integers-are-lousy-primary-keys/
你不应该使用 int 作为主键......这里有一篇关于它的文章：http: //techtrainedmonkey.com/2012/07/30/why-integers-are-lousy-primary-keys/
but if you do... set the field as identity and Sql Server will do it for you... check it out: http://msdn.microsoft.com/en-us/library/ms186775.aspx
但是如果你这样做...将字段设置为身份，Sql Server 会为你做...检查一下：http: //msdn.microsoft.com/en-us/library/ms186775.aspx
