sql-server-2005 - 如何在 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(除非它是最后一个。)这样做可能是一个复杂、昂贵且容易出错的过程。例如，您在此表中有一个 pkey，它可能会通过来自其他表的外键引用。如果您更改第一个表中 pkey 的值，那么您还必须在引用它的所有其他表中更改它。这意味着在更改等期间删除任何限制。

看起来您正在尝试创建一个最有可能呈现给最终用户的标识符。你可以继续使用你的函数来做到这一点，但不要让它成为主键。使用自动递增列作为主键，使用“P09-N”值作为单独的字段。然后，如果您想修改这些值，您可以在不影响表格设计的其余部分的情况下进行修改。

现在要在表被删除时更新标识符值，您可能需要在存储过程中使用游标。这是一个很好的overview on cursors .您还可以使用 CTE(通用表表达式)来执行 updating .

这是一个游标示例，其中 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

我没有做前导零逻辑，但你可以很容易地谷歌搜索。