如何在 Sql Server 2005 中创建自定义字母数字主键

Question

我写了这个算法 a 并且它是正确的： 表是：

create table PrimKeyTest (primarykeycolumn varchar(8), nextcolumn int)   
GO   
insert into PrimKeyTest values ('P09-0001', 1)   
GO   

我的功能是：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[GetSpecialPrimaryKey](@yearvalue int)   
returns nvarchar(8)   
as   

begin   

    declare @maxkey varchar(4)   
    declare @maxLength int, @maxkeylength int   

    set @maxLength = 4   

select @maxkey = ISNULL(cast(max(cast(substring(primaryKeycolumn, 5, 4) as integer)+1) as varchar),'1')   
 from PrimKeyTest   
        where substring(primaryKeycolumn, 2, 2) = substring(convert(varchar, @yearvalue), 3, 2)   

    set @maxkeylength = len(@maxkey)   

    while @maxkeylength < @maxLength   
    begin   
        set @maxkey = '0' + @maxkey   
        set @maxkeylength = len(@maxkey)   
    end   

    return 'P' + substring(convert(varchar, @yearvalue), 3, 2) + '-' + @maxkey   

end  

---

现在当我删除该表的最后一行时，新的最后一条记录给出正确的数字，例如。 P09-0001 P09-0002 P09-0003 P09-0004 P09-0005 但是当我删除该表的第二行时，主列的顺序不正确 例如。 P09-0001 P09-0003 P09-0004 P09-0005 你能帮助我吗？ 我想要这个：P09-0001 P09-0002 P09-0003 P09-0004

Answer 1

这实际上不是处理主键的好方法。这样做意味着每当删除一个密钥时（除非是最后一个密钥），就要“重新调整”所有密钥。这样做可能是一个复杂、成本高昂且容易出错的过程。例如，您在此表中有一个 pkey，可能会通过其他表的外键引用它。如果您更改第一个表中的 pkey 值，那么您还必须在引用它的所有其他表中更改它。这意味着在更改等期间放弃任何限制。

看起来您正在尝试创建一个很可能呈现给最终用户的标识符。您可以继续使用您的函数来执行此操作，但不要将其设为主键。使用自动递增列作为主键，使用“P09-N”值作为单独的字段。然后，如果您想修改这些值，您可以这样做，而不会影响表设计的其余部分。

现在，要在删除表时更新表的标识符值，您可能需要在存储过程中使用游标。这是一个很好的游标概述。您还可以使用 CTE（通用表表达式）来执行

这是一个光标示例，其中 Col1 是您的 pkey，Col2 是您要更改的标识符：

begin tran -- it's important to wrap this in a transaction!

declare @counter int
set @counter = 1
declare @val varchar(50)

DECLARE crs CURSOR 
FOR SELECT Col1 FROM TblTest ORDER By Col1

OPEN crs 
FETCH NEXT FROM crs INTO @val
WHILE @@FETCH_STATUS = 0
BEGIN

    UPDATE TblTest
    SET Col2 = 'P09-' + cast(@counter as varchar(50))
    WHERE Col1 = @val

    SET @counter = @counter + 1

    FETCH NEXT FROM crs INTO @val
END
CLOSE crs 
DEALLOCATE crs 

commit tran

我没有执行前导零逻辑，但您可以很容易地在 Google 上搜索到这一点。