SQL Server 两字段唯一复合键，第二字段自增

2021-11-30 00:00:00 sql unique sql-server auto-increment

我有以下问题，我想要复合主键，例如:

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

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

当我插入一个 base 时，id 会根据同一个 base 的前一个 id 自动递增

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

示例:

base   id
A      1
A      2
B      1
C      1

有没有办法当我说:INSERT INTO table(base) VALUES ('A')插入一个 id 3 的新记录，因为这是 base 'A' 的下一个 id?

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'?

结果表应该是:

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

是否可以完全在 DB 上执行此操作，因为如果以编程方式执行它可能会导致赛车状况.

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

编辑

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

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.

推荐答案

自从有人发布类似的问题以来，我一直在思考这个问题.第一个问题是 DB 不提供可分区"序列(这将根据不同的键重新启动/记住).第二个是 提供的 SEQUENCE 对象面向快速访问，并且无法回滚(即，您将获得间隙).这基本上排除了使用内置实用程序的可能性......这意味着我们必须自己推出.

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);

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

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.

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

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 ;;

注意:

  1. 您必须在序列化事务中运行它
  2. 事务必须与插入目标(发票)表的事务相同.
  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');

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

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:

  • Invoice_Sequence中维护序号的行
  • 新发票的 Invoice 行.
  • The row in Invoice_Sequence maintaining the sequence number
  • The row in Invoice for the new invoice.

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

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...

相关文章