如何让Branch ID依赖所选Branch生成带分支前缀的自定义自增主键

场景1：全局统一递增（实现最简单，无重复风险） #

该方案所有分支共享全局序号，ID全局唯一不会重复，仅需修改原计算列逻辑即可：

• 把原固定写死的'MI-'前缀替换为动态取分支名称前两位大写字符
• 调整补零逻辑为4位，完全匹配MI0001、CA0001的格式要求

CREATE TABLE dbo.tblCompany
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   -- 动态生成分支前缀+4位递增序号
   CompanyID AS UPPER(LEFT(Branch,2)) + RIGHT('0000' + CAST(ID AS VARCHAR(4)), 4) PERSISTED,
   FirstName VARCHAR(30),
   LastName VARCHAR(30),
   Branch VARCHAR(100) NOT NULL
   CHECK(Branch IN('MIAMI', 'CALIFORNIA', 'NEW YORK', 'SEATTLE'))
  )

如果存在分支前两位重复的情况（比如NEW YORK按原规则会生成NE，和其他分支冲突），可以用CASE语句自定义前缀映射：

CompanyID AS 
(CASE Branch
  WHEN 'NEW YORK' THEN 'NY'
  WHEN 'SEATTLE' THEN 'ST'
  ELSE UPPER(LEFT(Branch,2))
END) + RIGHT('0000' + CAST(ID AS VARCHAR(4)), 4) PERSISTED

场景2：各分支独立递增（每个分支序号都从0001开始） #

如果要求MIAMI第一条记录是MI0001，CALIFORNIA第一条记录也是CA0001，分支之间序号互不影响，需要调整表结构配合触发器实现：

1. 修改表结构 #

CREATE TABLE dbo.tblCompany
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   CompanyID VARCHAR(6) NULL, -- 改为普通存储字段，由触发器自动赋值
   FirstName VARCHAR(30),
   LastName VARCHAR(30),
   Branch VARCHAR(100) NOT NULL
   CHECK(Branch IN('MIAMI', 'CALIFORNIA', 'NEW YORK', 'SEATTLE'))
  )

2. 创建插入触发器 #

CREATE TRIGGER trg_tblCompany_GenerateID
ON dbo.tblCompany
AFTER INSERT
AS
BEGIN
  UPDATE t
  SET CompanyID = 
    -- 前缀生成规则可按需求自定义修改
    UPPER(LEFT(i.Branch,2)) + RIGHT('0000' + CAST(
    -- 统计当前分支的序号，实现独立递增
    (SELECT COUNT(*) FROM dbo.tblCompany WHERE Branch = i.Branch AND ID <= i.ID)
  AS VARCHAR(4)),4)
  FROM dbo.tblCompany t
  INNER JOIN inserted i ON t.ID = i.ID
END

测试效果 #

执行插入语句：

INSERT INTO dbo.tblCompany (FirstName, LastName, Branch)
VALUES ('John', 'Doe', 'MIAMI'), ('Jane', 'Smith', 'CALIFORNIA')

查询得到的CompanyID分别为MI0001、CA0001，完全符合需求。

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