在另一列的上下文中，SQL Server唯一的自动增量列 [英] SQL Server unique auto-increment column in the context of another column

问题描述

假设该表有两列:

ParentEntityId int foreign key
Number int

ParentEntityId是另一个表的外键.

ParentEntityId is a foreign key to another table.

Number是 local 身份，即在单个ParentEntityId中是唯一的.

Number is a local identity, i.e. it is unique within single ParentEntityId.

通过在这两列上使用唯一键可以轻松实现唯一性.

Uniqueness is easily achieved via unique key over these two columns.

如何在插入的ParentEntityId上下文中自动增加Number?

How to make Number be automatically incremented in the context of the ParentEntityId on insert?

附录1

为澄清问题，这里是一个摘要.

To clarify the problem, here is an abstract.

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

ParentEntity has multiple ChildEntity, and each ChiildEntity should have an unique incremental Number in the context of its ParentEntity.

附录2

以客户的身份对待ParentEntity.

将ChildEntity处理为订单.

因此，每个客户的订单应编号为1、2、3，依此类推.

So, orders for every customer should be numbered 1, 2, 3 and so on.

推荐答案

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

Well, there's no native support for this type of column, but you could implement it using a trigger:

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个月后进行修改时，很难理解它们).

Not tested but I'm pretty sure it'll work. If you have a primary key, you could also implement this as an AFTER trigger (I dislike using INSTEAD OF triggers, they're harder to understand when you need to modify them 6 months later).

仅说明此处发生的情况:

Just to explain what's going on here:

• SERIALIZABLE是最严格的隔离模式；它保证一次只能有一个数据库事务执行这些语句，这是我们保证该序列"完整性所需要的.请注意，这不可逆地促进了整个交易，因此您不希望在长期运行的交易中使用此交易.

• SERIALIZABLE is the strictest isolation mode; it guarantees that only one database transaction at a time can execute these statements, which we need in order to guarantee the integrity of this "sequence." Note that this irreversibly promotes the entire transaction, so you won't want to use this inside of a long-running transaction.

CTE收集每个父ID已经使用的最高编号；

The CTE picks up the highest number already used for each parent ID;

ROW_NUMBER从数字1开始为每个父ID(PARTITION BY)生成一个唯一的序列；如果有一个序列可以获取新序列，则将其添加到先前的最大值.

ROW_NUMBER generates a unique sequence for each parent ID (PARTITION BY) starting from the number 1; we add this to the previous maximum if there is one to get the new sequence.

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

I probably should also mention that if you only ever need to insert one new child entity at a time, you're better off just funneling those operations through a stored procedure instead of using a trigger - you'll definitely get better performance out of it. This is how it's currently done with hierarchyid columns in SQL '08.

这篇关于在另一列的上下文中，SQL Server唯一的自动增量列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！