增加主键值 [英] Incrementing primary key value

问题描述

您好，请帮我提供一个代码示例，我可以用来在数据库中的数据表中递增/生成下一个主键值，而不必打开我的表，而是每次我添加新记录时都会自动添加它使用表单控件(例如textBoxes和Buttons)访问我的表

谢谢
Katumba martin

Hi there, please help me with a code sample i can use to increment/generate the next primary key value in my datatables in a database without having to open my tables but instead have it automatically added each time i add a new record to my table using the form controls e.g textBoxes and Buttons

Thanks
Katumba martin

推荐答案

CREATE TABLE test (
    testID int IDENTITY (1, 1) NOT NULL,
    TestName nvarchar(50) NOT NULL
)

使用SCOPE_IDENTITY函数返回您插入的值.

不要使用@@IDENTITY返回标识值，因为由于范围问题，不能保证返回您认为插入的标识；如果您有一个插入另一个身份的触发器，那么这会弄乱@@ IDENTITY返回的值.

请注意，如果删除任何行或所有行，则无法使用此删除的ID，它将从其离开的位置开始.如果删除所有行，则必须显式重置任何给定的数字(或从头开始再次重置).

我的建议是使用COUNT(*)和MAX(testID)如果检查count是否为零，则id为1，否则id为MAX(testID) + 1，
仅仅一个SELECT语句.

Use SCOPE_IDENTITY function for returning the value you have inserted.

Don''t use @@IDENTITY to return the identity value because it is not guaranteed to return the identity you think you inserted due to scoping issues; if you have a trigger that inserts another identity then this would screw up the value returned by @@IDENTITY.

Note that if you delete any row or all the rows then you cannot use this deleted ID, it will start from which it left. If you delete all the rows then you have to reset explicitly any given number (or again from start).

My suggestions is that use COUNT(*) and MAX(testID) if check whether count is zero then id will be 1 else id will be MAX(testID) + 1,
just one single SELECT statement.

使用数字自动增量标识列会产生很多问题.我在本文中谈到了其中的一些内容，尤其是对于那些经过大量修改的表.那篇文章介绍了Oracle和PostgreSQL，所以我不会重新发布DDL.

对于SQL Server，您将需要这样的内容.

Using numeric autoincrement identity columns create a ton of problems. I touch of some of them in this article especially for tables that heavily modified. That article covers Oracle and PostgreSQL, so I won''t repost the DDL.

For SQL Server you would need something like this.

/****** Object:  Table [dbo].[Issuer]    Script Date: 02/18/2010 08:36:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Issuer](
    [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Issuer] PRIMARY KEY CLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_Issuer] UNIQUE NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Issuer] ADD  CONSTRAINT [DF_Issuer_ID]  DEFAULT (newid()) FOR [ID]
GO

如您所见，行标识列不是有意义的数据，因此不是大多数时间要搜索的内容.因此，主键是人类有意义的列名称"


此外，如果要在代码中生成ID，则IDENTITY列要求您

As you can see, the row identity column is not the human meaningful data, thus, not what is going to be searched on most of the time. Therefore, the primary key is the human meaningful column "Name"


Additionally if you want to generate your IDs in code, IDENTITY columns require that you Enable Identity Insert. On the other hand, uniqueidentifier columns with the default as shown above mean you can insert your own value from Guid.NewGuid().

NOTE: Using any kind of sequential id generation in code (like MAX(ID)+1) could cause conflicts if multiple inserts are happening at the same time (in other words 2 processes or threads could generate the next id as 5). Where as using a Guid.NewGuid generates a pseudo-random number that has a 1 in 2¹²² chance of duplication.

您好，
在数据库中设计数据表期间，
请执行以下操作.
1.在设计时单击主键字段
2.您将在表格设计表面下方看到该垂直列的属性窗口.
3.将身份"值设置为是".
4.将身份增量设置为1.

希望对您有所帮助:-\

Hi there,
During the design time of your data table in the database
Do the following.
1.click on the primary key field during design time
2. You will see the properties window for that perticular column below the table design surface.
3. Set the Identity value to Yes.
4. Set the Identity Increment to 1.

Hope it helps :-\

这篇关于增加主键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！