SQL Server 两个字段的唯一组合键，第二个字段自动递增

Question

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

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

当我插入base时，id将根据相同base的先前id自动递增

示例:

base   id
A      1
A      2
B      1
C      1

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

结果表应该是:

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

是否可以在数据库上执行此操作，因为如果以编程方式完成，可能会导致赛车状况。

编辑

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

Answer 1

自从有人提出类似问题后，我就一直在思考这个问题。第一个问题是数据库不提供“可分区”序列(将根据不同的键重新启动/记住)。第二个是提供的 SEQUENCE 对象是围绕快速访问而设计的，并且不能回滚(即，您将得到间隙)。这本质上排除了使用内置实用程序......这意味着我们必须推出自己的实用程序。

我们首先需要一个表来存储序列号。这可能相当简单:

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

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

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

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. 交易必须与插入目标(发票)表的交易相同。

没错，在开具发票号码时，您仍然会受到每个企业的阻止。如果发票编号必须是连续的、没有间隙，则您无法避免这种情况 - 在实际提交该行之前，它可能会回滚，这意味着发票编号不会被发出。

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

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 中新发票的行。

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