SQL Server如何创建第二部分可按外键分组自增重置的复合主键

前置表结构 #

-- 主表结构
CREATE TABLE Audit_Header (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    CreateTime DATETIME DEFAULT GETDATE()
    -- 其他主表业务字段按需补充
);

-- 明细表结构，设置AuditID+SeqNo为复合主键
CREATE TABLE Audit_Detail (
    AuditID INT NOT NULL FOREIGN KEY REFERENCES Audit_Header(AuditID),
    SeqNo INT NOT NULL,
    DetailContent NVARCHAR(1000)
    -- 其他明细业务字段按需补充
    PRIMARY KEY (AuditID, SeqNo)
);

方案1：INSTEAD OF INSERT触发器（全版本兼容，高并发友好） #

无需手动处理SeqNo生成逻辑，插入明细时触发器自动按当前AuditID的最大SeqNo计算新值，支持单次插入同AuditID的多条明细：

CREATE TRIGGER trg_AuditDetail_AutoGenSeqNo
ON Audit_Detail
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Audit_Detail (AuditID, SeqNo, DetailContent)
    SELECT
        i.AuditID,
        ISNULL(ad.MaxSeqNo, 0) + ROW_NUMBER() OVER(PARTITION BY i.AuditID ORDER BY (SELECT 1)),
        i.DetailContent
    FROM inserted i
    LEFT JOIN (
        SELECT AuditID, MAX(SeqNo) AS MaxSeqNo
        FROM Audit_Detail
        GROUP BY AuditID
    ) ad ON i.AuditID = ad.AuditID;
END

使用示例 #

-- 插入主表记录，获取生成的AuditID
INSERT INTO Audit_Header DEFAULT VALUES;
DECLARE @AuditID1 INT = SCOPE_IDENTITY();
INSERT INTO Audit_Header DEFAULT VALUES;
DECLARE @AuditID2 INT = SCOPE_IDENTITY();

-- 插入明细时不需要指定SeqNo字段
INSERT INTO Audit_Detail (AuditID, DetailContent)
VALUES
(@AuditID1, '第一条明细'),
(@AuditID1, '第二条明细'),
(@AuditID2, '第一条明细'),
(@AuditID2, '第二条明细');

查询结果会自动生成符合需求的分组自增SeqNo，不会出现全局自增的情况。

方案2：插入时动态计算（适合低并发、单条插入场景） #

如果不想使用触发器，可以在插入SQL中手动计算SeqNo：

DECLARE @TargetAuditID INT = 12345;
INSERT INTO Audit_Detail (AuditID, SeqNo, DetailContent)
SELECT
    @TargetAuditID,
    ISNULL(MAX(SeqNo), 0) + 1,
    '新增明细内容'
FROM Audit_Detail
WHERE AuditID = @TargetAuditID;

注意：该方案在高并发场景下可能出现主键冲突，需要配合事务和SERIALIZABLE隔离级别使用

注意事项 #

• Identity列是全局自增属性，无法实现分组自增的需求，不要使用Identity定义SeqNo字段
• 上述方案默认不会填补删除明细产生的SeqNo空缺，新插入的明细会继续从当前最大SeqNo向后生成，符合绝大多数业务场景要求，如需补号需要单独编写逻辑处理
• 复合主键自带AuditID索引，SeqNo计算的查询性能足够，无需额外加索引

内容的提问来源于stack exchange，提问作者CraigBob