如何将auto_increment添加到SQL Server 2008中的列 [英] How do I add auto_increment to a column in SQL Server 2008

问题描述

我正在使用SQL Server 2008，并且我正在使用的数据库表的主键不是IDENTITY列(不确定原因).我需要改变这一点.

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

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

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的新手-关于我缺少的任何想法?

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

这是创建表

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

推荐答案

举例说明马丁的观点:

和 PS:-正如Mikael Eriksson正确提到的(和文档很好)，只要您正在处理的那一列有一个 Identity Specification ，它就会一直显示为灰色.默认约束.

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

这篇关于如何将auto_increment添加到SQL Server 2008中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！