如何将 auto_increment 添加到 SQL Server 2008 中的列

How do I add auto_increment to a column in SQL Server 2008

提问by Matt Elhotiby

I am using SQL Server 2008 and a primary key of a database table I am using is not an IDENTITYcolumn (not sure why). I need to change that.

我使用的是 SQL Server 2008，我使用的数据库表的主键不是IDENTITY列（不知道为什么）。我需要改变这一点。

I am in SQL Server Management Studio in design view, under column properties and for some reason I can't change the identity specifications to Yes.

我在 SQL Server Management Studio 的设计视图中，在列属性下，由于某种原因，我无法将标识规范更改为Yes.

Is there something that I am missing.. I am new to SQL Server - any ideas on what I am missing??

有什么我遗漏的吗..我是 SQL Server 的新手 - 关于我遗漏的任何想法？

Here is the create table

这是创建表

CREATE TABLE [dbo].[AR_Transactions](
       [Trans_ID] [bigint] NOT NULL,
       [DateTime] [datetime] NOT NULL,
       [Cashier_ID] [nvarchar](50) NULL,
       [CustNum] [nvarchar](12) NOT NULL,
       [Trans_Type] [nvarchar](2) NOT NULL,
       [Prev_Cust_Balance] [money] NULL,
       [Prev_Inv_Balance] [money] NULL,
       [Trans_Amount] [money] NOT NULL,
       [Payment_Method] [nvarchar](4) NULL,
       [Payment_Info] [nvarchar](20) NULL,
       [Description] [nvarchar](38) NULL,
       [Invoice_Number] [bigint] NOT NULL,
       [Store_ID] [nvarchar](10) NOT NULL,
       [Dirty] [bit] NOT NULL,
       [Station_ID] [nvarchar](5) NULL,
       [Payment_Type] [smallint] NULL,

CONSTRAINT [pkAR_Transactions] 
       PRIMARY KEY CLUSTERED([Store_ID] ASC, [Trans_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]

ALTER TABLE [dbo].[AR_Transactions] 
    ADD CONSTRAINT [DF_AR_Transactions_Trans_ID_AR_Transactions] 
    DEFAULT ((0)) FOR [Trans_ID]

ALTER TABLE [dbo].[AR_Transactions] 
    ADD CONSTRAINT [DF_AR_Transactions_Invoice_Number_AR_Transactions] 
    DEFAULT ((0)) FOR [Invoice_Number]

Here is the query that I need to run... its a complete hack to try to auto-increment my inserts myself

这是我需要运行的查询......它是一个完整的技巧，试图自己自动增加我的插入

BEGIN TRANSACTION 

INSERT INTO 
        [cresql].[dbo].[AR_Transactions](Trans_ID, DateTime , Dirty, Store_ID, Trans_Type,  
            Cashier_ID, CustNum, Trans_Amount, Prev_Cust_Balance) 
        SELECT  
            (SELECT MAX(Trans_ID ) + 1 FROM [cresql].[dbo].[AR_Transactions]), 
            DATEADD(MINUTE, -30, Getdate()), 1, 1001, 'C', 100199, CustNum,
            -Acct_Balance, Acct_Balance 
    FROM  [cresql].[dbo].[Customer] 
        WHERE Acct_Balance <> 0  

UPDATE [cresql].[dbo].[Customer] 
    SET Acct_Balance = 0 
WHERE Acct_Balance <> 0  

COMMIT TRANSACTION

回答by marc_s

To illustrate Martin's point:

为了说明马丁的观点：

And PS:- as Mikael Eriksson rightfully mentions (and documents nicely), this Identity Specificationremains grayed out as long as that column you're working on has a default constraint.

而PS：-为的Mikael埃里克森理所当然地提到（和很好的文件），该标识规范保持变灰，只要该列你的工作有一个默认的约束。

回答by Martin Smith

You need to expand the "Identity Specification" node to change it via the (Is Identity)property.

您需要展开“身份规范”节点以通过(Is Identity)属性更改它。

This will rebuild the table so you might also need to go into Tools -> Options -> Designers -> Prevent saving changes that require table re-creation.

这将重建表，因此您可能还需要进入Tools -> Options -> Designers -> Prevent saving changes that require table re-creation.

This can be an extremely time consuming operation on large tables as well as entailing a lot of logging and locking. To perform this operation on a large table see my answer here.

这在大型表上可能是非常耗时的操作，并且需要大量的日志记录和锁定。要在大表上执行此操作，请在此处查看我的回答。

回答by Mikael Eriksson

Remove the default constraint of column Trans_ID first. Then you can set Is Identityto Yes in the designer.

首先删除列 Trans_ID 的默认约束。然后您可以Is Identity在设计器中设置为 Yes。

This is properties for column Trans_ID in your table AR_Transactions. (Is Identity)is disabled:

这是表 AR_Transactions 中 Trans_ID 列的属性。(Is Identity)被禁用：

Remove the default constraint and (Is Identity)is no longer disabled:

删除默认约束， (Is Identity)不再禁用：

Set to yes and save. Default Value or Bindingis disabled instead:

设置为是并保存。Default Value or Binding被禁用：

回答by Nicholas Carey

You can't use ALTER TABLE ... ALTER COLUMNto modify a column to have an identity property. You'll need to

您不能用于ALTER TABLE ... ALTER COLUMN修改列以具有标识属性。你需要

• drop the primary key constraint and any foreign key constraints referencing the column in question in your table.
• add a new column with the identityproperty. It should have the same type (int, I presume) as the existing column.
• update the table to seed the new column with the values of the existing column.
• alter the new column to make it non-nullable.
• drop the old/existing column.
• rename the new column so that its name is the same as that of the old column.
• Recreate the primary key and foreign key references you dropped in the 1st step.

• 删除主键约束和任何引用表中相关列的外键约束。
• 添加具有该identity属性的新列。它应该int与现有列具有相同的类型（我认为）。
• 更新表以使用现有列的值作为新列的种子。
• 更改新列以使其不可为空。
• 删除旧的/现有的列。
• 重命名新列，使其名称与旧列的名称相同。
• 重新创建您在第一步中删除的主键和外键引用。

Simple! Or something.

简单的！或者其他的东西。

回答by Maddy Selva

CREATE TABLE [dbo].[AR_Transactions](
       [Trans_ID] [bigint] IDENTITY(1,1) NOT NULL,
       [DateTime] [datetime] NOT NULL,
       [Cashier_ID] [nvarchar](50) NULL,
       [CustNum] [nvarchar](12) NOT NULL,
       [Trans_Type] [nvarchar](2) NOT NULL,
       [Prev_Cust_Balance] [money] NULL,
       [Prev_Inv_Balance] [money] NULL,
       [Trans_Amount] [money] NOT NULL,
       [Payment_Method] [nvarchar](4) NULL,
       [Payment_Info] [nvarchar](20) NULL,
       [Description] [nvarchar](38) NULL,
       [Invoice_Number] [bigint] NOT NULL,
       [Store_ID] [nvarchar](10) NOT NULL,
       [Dirty] [bit] NOT NULL,
       [Station_ID] [nvarchar](5) NULL,
       [Payment_Type] [smallint] NULL,

CONSTRAINT [pkAR_Transactions] 
       PRIMARY KEY CLUSTERED([Store_ID] ASC, [Trans_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]

ALTER TABLE [dbo].[AR_Transactions] 
    ADD CONSTRAINT [DF_AR_Transactions_Trans_ID_AR_Transactions] 
    DEFAULT ((0)) FOR [Trans_ID]

ALTER TABLE [dbo].[AR_Transactions] 
    ADD CONSTRAINT [DF_AR_Transactions_Invoice_Number_AR_Transactions] 
    DEFAULT ((0)) FOR [Invoice_Number]