如何自动生成复杂的标识符并确保唯一性

我收到了一个非常具体的业务需求，但我在将其转换为在我们的数据库中工作时遇到了麻烦。

我们

有多个部门，他们都处理我们所谓的文件。每个文件都有一个唯一的标识符，但分配此标识符的方式取决于与文件关联的部门。

部门 ABC、DEF 和 GHI 需要系统按照某种模式为他们提供下一个标识符......而部门 JKL 和 MNO 需要能够指定自己的标识符。

生成的文件编号是半复杂的这一事实进一步加剧了这种情况。他们的文件编号遵循以下模式：

[a-z]{3,4}d{2}-d{4}

（一个 3 或 4 个字母的前缀，对应于部门的两位数字对应于年份，一个破折号后跟一个四位生成的数字 - 即 ABC13-0001）

破折号前的部分很容易生成...文件表对具有前缀列的部门表具有强制性外键引用，我可以很容易地获取年份。这是我似乎无法解决的破折号之后的部分。

这是一个四位数的标识符。每个部门都需要下一个生成的 ID 尽可能与他们的部门顺序排列（我说尽可能顺序，因为我知道即使是身份规范也会留下空白）。最重要的是，我们需要每年将其重置回 0001。所有这些，同时确保没有重复，这是所有这一切中最重要的部分。

因此，我们只有一个文件表供所有这些部门使用。因此，为了能够处理 JKL 和 MNO，我将 FileNumber 字段设置为具有唯一约束的 varchar（12）。他们可以输入他们需要的任何内容，只要它是唯一的。这让我知道如何为其他部门生成唯一的文件编号。

我的第一直觉是给文件表一个代理标识主键，以确保即使生成的文件编号出现问题，也保证每条记录都有一个唯一的标识符。

然后，我将创建一个单行表，每个部门有两列，一列用于数字，一列用于日期。该号码将是给定部门（作为整数）的 4 位标识符后缀的最后使用号码，日期将是分配日期。存储日期可以让我检查自提取最后一个 id 以来年份是否发生了变化，以便我可以分配 1，而不是 lastid + 1。

然后，文件表上的插入触发器将使用以下内容生成文件 ID：

表的外键，用于获取部门前缀
转换（VARCHAR， YEAR（ GETDATE（）） % 100）以提取当前两位数的年份
选择上述实用程序表以获取最后一个 id + 1（如果当前年份与上次更新日期的年份不同，则重置为 1）。

触发器最终将使用上次使用的 id 和日期更新实用程序表。

从理论上讲，我认为这会起作用...并且对文件 ID 列的唯一约束将阻止在生成的文件 ID 已存在的地方插入。但它感觉很脆弱，我可以预见这种独特的约束是一把双刃剑，如果触发器无法更新实用程序表，它可能会阻止部门创建新文件。毕竟，如果没有，那么下次生成文件编号时，它将尝试使用相同的生成 id，并失败。一定有更好的方法。

我的另一个想法是为每个部门提供一个表，只有一个标识整数列和非空日期字段，默认值为（getdate（））...并让文件表上的触发器插入一个新行，并使用该 ID。它还将负责删除给定部门 id 表中的所有行，并在新的一年重置身份。这对我来说感觉更安全，但是我有 5 个实用程序表（每个部门 1 个自动生成 id），最多 9999 条记录，只是为了生成一个 id。

我错过了一些简单的东西吗？我这样做的方式是否正确？我只是忍不住想，一定有更简单的方法。我尝试让 SQL 服务器对此负责是对的，还是应该尝试在我将在此数据库之上构建的桌面应用程序中执行此操作？

所以，我认为你可能把这件事复杂化了。我也有可能误解了要求。我不认为您需要单独的表或任何东西，您只需要检查现有 ID 并增加它们。SQLFiddle现在似乎不起作用，但这是我在本地数据库中想出的一种方法：

create table dept_ids (
  id varchar(12) primary key
  )
insert into dept_ids values('ABC13-0001')
insert into dept_ids values('DEF13-0001')
insert into dept_ids values('GHI13-0001')
insert into dept_ids values('JKL13-0001')
insert into dept_ids values('ABC13-0002')
declare 
    @dept varchar(4)
    , @year varchar(2)
    , @prefix varchar(8)
    , @new_seq int
set @dept = 'ABC'
select @year = right(cast(DATEPART(yy, getdate()) as varchar(4)), 2)
set @prefix = @dept + @year + '-'
select @new_seq = isnull(right(max(id), 4), 0) + 1 from dept_ids where id like @prefix + '%'
select new_id = @prefix + right(replicate('0', 4) + cast(@new_seq as varchar(4)), 4)

当然，这可以处理不同的部门，以及使用 getdate（）和 ISNULL 检查的年份场景。对于可以输入自己的序列值的部门，您只需为该参数添加空检查，并跳过生成此值（如果存在）。

如果我过于简单化，请随时告诉我，我会调整。

相关内容

最新更新

热门标签：