如何在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，但当我删除此表的第2行时，主列的顺序出现错误。你能帮我吗？我要这个: 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

我没有使用领先的零逻辑，但你可以很容易地在谷歌上搜索。