SQL Server 在另一列的上下文中唯一的自动增量列

Question

假设该表有两列：

ParentEntityId int foreign key
Number int

ParentEntityId 是另一个表的外键。

Number 是一个本地身份，即它在单个ParentEntityId 中是唯一的。

通过这两列上的唯一键可以轻松实现唯一性。

如何使 Number 在插入时在 ParentEntityId 的上下文中自动递增？

---

附录 1

澄清问题，这是一个摘要。

ParentEntity 具有多个 ChildEntity，并且每个 ChiildEntity 在其 上下文中应该有一个唯一的增量 Number父实体。

---

附录 2

将 ParentEntity 视为客户。

将 ChildEntity 视为 Order。

因此，每个客户的订单应编号为 1、2、3 等。

Answer 1

好吧，这种类型的列没有本机支持，但您可以使用触发器来实现它：

CREATE TRIGGER tr_MyTable_Number
ON MyTable
INSTEAD OF INSERT
AS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN;

WITH MaxNumbers_CTE AS
(
    SELECT ParentEntityID, MAX(Number) AS Number
    FROM MyTable
    WHERE ParentEntityID IN (SELECT ParentEntityID FROM inserted)
)
INSERT MyTable (ParentEntityID, Number)
    SELECT
        i.ParentEntityID,
        ROW_NUMBER() OVER
        (
            PARTITION BY i.ParentEntityID
            ORDER BY (SELECT 1)
        ) + ISNULL(m.Number, 0) AS Number
    FROM inserted i
    LEFT JOIN MaxNumbers_CTE m
        ON m.ParentEntityID = i.ParentEntityID

COMMIT

未经测试，但我很确定它会起作用。如果您有主键，您也可以将其实现为 AFTER 触发器（我不喜欢使用 INSTEAD OF 触发器，当您需要修改它们时它们更难理解6个月后）。

只是为了解释这里发生的事情：

• SERIALIZABLE 是最严格的隔离模式；它保证一次只有一个数据库事务可以执行这些语句，这是我们为了保证这一“序列”的完整性所需要的。请注意，这会不可逆地提升整个事务，因此您不会希望在长时间运行的事务中使用它。

• CTE 选取每个父 ID 已使用的最高编号；

• ROW_NUMBER为每个父ID（PARTITION BY）生成一个从数字1开始的唯一序列；如果有一个可以获取新序列，我们会将其添加到前一个最大值。

我可能还应该提到，如果您一次只需要插入一个新的子实体，那么您最好通过存储过程而不是使用触发器来集中这些操作 - 您肯定会从中获得更好的性能。这就是目前在 SQL '08 中使用 hierarchyid 列完成的方式。

Answer 2

需要添加 OUTPUT 子句来触发 Linq to SQL 兼容性。

例如：

INSERT MyTable (ParentEntityID, Number)
OUTPUT inserted.* 
SELECT
  i.ParentEntityID,
  ROW_NUMBER() OVER
  (
   PARTITION BY i.ParentEntityID
   ORDER BY (SELECT 1)
  ) + ISNULL(m.Number, 0) AS Number
FROM inserted i
LEFT JOIN MaxNumbers_CTE m
ON m.ParentEntityID = i.ParentEntityID

Answer 3

据我了解，这解决了问题：:-)

DECLARE @foreignKey int
SET @foreignKey = 1  -- or however you get this

INSERT Tbl (ParentEntityId, Number) 
VALUES (@foreignKey, ISNULL((SELECT MAX(Number) FROM Tbl WHERE ParentEntityId = @foreignKey), 0) + 1)