SQL Server具有两字段自动递增的两字段的唯一复合键 [英] SQL Server Unique Composite Key of Two Field With Second Field Auto-Increment

问题描述

我遇到以下问题，我想使用复合主键:

PRIMARY KEY (`base`, `id`);

当我插入base时，对于相同的base

，该ID将基于前一个id自动递增

示例:

base   id
A      1
A      2
B      1
C      1

当我说: INSERT INTO table(base) VALUES ('A') 用id 3插入新记录，因为那是base'A'的下一个ID?

结果表应为:

base   id
A      1
A      2
B      1
C      1
A      3

是否有可能在DB上完全做到这一点，因为如果以编程方式完成，可能会导致赛车状况.

编辑

base当前代表公司，id代表发票编号.每个公司应该有自动递增的发票编号，但是在某些情况下，两个公司的发票编号也相同.在公司登录的用户应该能够通过这些发票编号进行排序，过滤和搜索.

解决方案

自从有人发布了类似的问题以来，我一直在思考这个问题.第一个问题是DB不提供可分区"序列(该序列将基于不同的键重新启动/记住).第二个问题是提供的 对象SEQUENCE是围绕快速访问而设计的，不能回滚(即，您将留空).从本质上讲，这可以使用内置实用程序排除……这意味着我们必须自己滚动.

我们首先需要的是一个用于存储序列号的表.这可以很简单:

CREATE TABLE Invoice_Sequence (base CHAR(1) PRIMARY KEY CLUSTERED,
                               invoiceNumber INTEGER);

实际上，base列应该是对要为其创建发票的业务/实体的表/ID的外键引用.在此表中，您希望每个发布实体的条目都是唯一的.

接下来，您需要一个存储的proc，它将带一个键(base)并吐出序列中的下一个数字(invoiceNumber).必要的密钥集会有所不同(即某些发票号必须包含发行的年份或完整日期)，但是这种情况的基本格式如下:

CREATE PROCEDURE Next_Invoice_Number @baseKey CHAR(1), 
                                     @invoiceNumber INTEGER OUTPUT 
AS MERGE INTO Invoice_Sequence Stored
              USING (VALUES (@baseKey)) Incoming(base)
                 ON Incoming.base = Stored.base
   WHEN MATCHED THEN UPDATE SET Stored.invoiceNumber = Stored.invoiceNumber + 1
   WHEN NOT MATCHED BY TARGET THEN INSERT (base) VALUES(@baseKey)
   OUTPUT INSERTED.invoiceNumber ;;

请注意:

1. 您必须在序列化交易中运行它
2. 交易必须必须与要插入到目标(发票)表的交易相同.

是的，在开具发票编号时，您仍然会阻止每笔业务.如果发票号必须是连续的且没有空格，您将避免-在实际提交该行之前，可能会回滚该行，这意味着不会发出发票号./p>

现在，由于您不想记住调用该条目的过程，因此将其包装在触发器中:

CREATE TRIGGER Populate_Invoice_Number ON Invoice INSTEAD OF INSERT
AS 
  DECLARE @invoiceNumber INTEGER
  BEGIN
    EXEC Next_Invoice_Number Inserted.base, @invoiceNumber OUTPUT
    INSERT INTO Invoice (base, invoiceNumber) 
                VALUES (Inserted.base, @invoiceNumber)
  END

(显然，您有更多列，包括其他应自动填充的列-您需要填写它们)
...您可以通过简单地说:

INSERT INTO Invoice (base) VALUES('A');

那我们做了什么?通常，所有这些工作都是为了减少事务锁定的行数.在提交此INSERT之前，只有两行被锁定:

• Invoice_Sequence中的行保持序列号
• Invoice中新发票的行.

特定base的所有其他行都是免费的-可以随意更新或查询(从这种系统中删除信息会使会计师感到紧张).您可能需要确定当查询通常包含待处理的发票时应该怎么办...

I have the following problem, I want to have Composite Primary Key like:

PRIMARY KEY (`base`, `id`);

for which when I insert a base the id to be auto-incremented based on the previous id for the same base

Example:

base   id
A      1
A      2
B      1
C      1

Is there a way when I say: INSERT INTO table(base) VALUES ('A') to insert a new record with id 3 because that is the next id for base 'A'?

The resulting table should be:

base   id
A      1
A      2
B      1
C      1
A      3

Is it possible to do it on the DB exactly since if done programmatically it could cause racing conditions.

EDIT

The base currently represents a company, the id represents invoice number. There should be auto-incrementing invoice numbers for each company but there could be cases where two companies have invoices with the same number. Users logged with a company should be able to sort, filter and search by those invoice numbers.

解决方案

Ever since someone posted a similar question, I've been pondering this. The first problem is that DBs don't provide "partitionable" sequences (that would restart/remember based on different keys). The second is that the SEQUENCE objects that are provided are geared around fast access, and can't be rolled back (ie, you will get gaps). This essentially this rules out using a built-in utility... meaning we have to roll our own.

The first thing we're going to need is a table to store our sequence numbers. This can be fairly simple:

CREATE TABLE Invoice_Sequence (base CHAR(1) PRIMARY KEY CLUSTERED,
                               invoiceNumber INTEGER);

In reality the base column should be a foreign-key reference to whatever table/id defines the business(es)/entities you're issuing invoices for. In this table, you want entries to be unique per issued-entity.

Next, you want a stored proc that will take a key (base) and spit out the next number in the sequence (invoiceNumber). The set of keys necessary will vary (ie, some invoice numbers must contain the year or full date of issue), but the base form for this situation is as follows:

CREATE PROCEDURE Next_Invoice_Number @baseKey CHAR(1), 
                                     @invoiceNumber INTEGER OUTPUT 
AS MERGE INTO Invoice_Sequence Stored
              USING (VALUES (@baseKey)) Incoming(base)
                 ON Incoming.base = Stored.base
   WHEN MATCHED THEN UPDATE SET Stored.invoiceNumber = Stored.invoiceNumber + 1
   WHEN NOT MATCHED BY TARGET THEN INSERT (base) VALUES(@baseKey)
   OUTPUT INSERTED.invoiceNumber ;;

Note that:

1. You must run this in a serialized transaction
2. The transaction must be the same one that's inserting into the destination (invoice) table.

That's right, you'll still get blocking per-business when issuing invoice numbers. You can't avoid this if invoice numbers must be sequential, with no gaps - until the row is actually committed, it might be rolled back, meaning that the invoice number wouldn't have been issued.

Now, since you don't want to have to remember to call the procedure for the entry, wrap it up in a trigger:

CREATE TRIGGER Populate_Invoice_Number ON Invoice INSTEAD OF INSERT
AS 
  DECLARE @invoiceNumber INTEGER
  BEGIN
    EXEC Next_Invoice_Number Inserted.base, @invoiceNumber OUTPUT
    INSERT INTO Invoice (base, invoiceNumber) 
                VALUES (Inserted.base, @invoiceNumber)
  END

(obviously, you have more columns, including others that should be auto-populated - you'll need to fill them in)
...which you can then use by simply saying:

INSERT INTO Invoice (base) VALUES('A');

So what have we done? Mostly, all this work was about shrinking the number of rows locked by a transaction. Until this INSERT is committed, there are only two rows locked:

• The row in Invoice_Sequence maintaining the sequence number
• The row in Invoice for the new invoice.

All other rows for a particular base are free - they can be updated or queried at will (deleting information out of this kind of system tends to make accountants nervous). You probably need to decide what should happen when queries would normally include the pending invoice...

这篇关于SQL Server具有两字段自动递增的两字段的唯一复合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！