记录组的类似身份的自动递增 ID [英] Identity-like auto incrementing ID for groups of records

问题描述

我有一个通过父项的主键关联到父项的子表.目前，子表的标准 ID 为 int IDENTITY(1,1).这符合我的目的，不会改变，但对用户来说，当他们查看与父行关联的子行时，他们会看到不相交的 ID 列表.

I have a child table associated to the parent by the parent's primary key. Currently, the child table has a standard ID of int IDENTITY(1,1). This is fine for my purposes and that will not change but to the user, they'll be seeing lists of disjointed IDs when they view the child rows associated to the parent.

我想要一个从 1 开始并为每组记录递增(如标识列)的显示 ID.

我的子表看起来像这样；

My child table would look something like this;

ChildID-PK ParentID-FK DisplayID
 1          1           1
 2          1           2
 3          2           1
 4          3           1
 5          1           3

可能的解决方案；

• 插入记录时，SELECT MAX(DisplayID) + 1 FROM tbl WHERE ParentID = @ParentID 用作新记录的DisplayID.如果多人同时编辑一组记录，我可以看出这会带来问题.
• 使用 ROW_NUMBER() 获取 DisplayID，但在这种情况下，如果要删除记录，现有记录的 DisplayID 可能会更改.这不会发生，因为用户在输入数据时可能会参考特定的 DisplayID.

• When inserting a record, SELECT MAX(DisplayID) + 1 FROM tbl WHERE ParentID = @ParentID to use as the new records DisplayID. I could see this being problematic if many people are editing a group of records at once.
• Use ROW_NUMBER() to get the DisplayID but, in this case, if a record were to be deleted the DisplayID for existing records could change. This can't happen because users may be referring to specific DisplayIDs when entering data.

推荐答案

我能想到的最安全的方法是创建类似于 Oracle 的序列对象的东西.在这个 Microsoft SQL Server 开发客户咨询团队博客文章(我更喜欢选项 2).

The safest way I can think of, is creating something similar to Oracle's sequence object. There is a good sample of doing so in this Microsoft SQL Server Development Customer Advisory Team blog post (I prefer option 2).

您应该为插入的每个父行创建一个序列:

You should create a sequence for every parent row inserted:

EXEC usp_CreateNewSeq N'ParentSeq' + @parentId

并获取关联表中每一行的下一个序列:

and get the next sequence for each row in your association table:

INSERT INTO [table] VALUES (@childId, @parentId, EXEC 'GetNewSeqVal_'+ @parentId)

我不确定自己的语法，也无法检查知道，所以请随时纠正我.

I'm not sure about my syntax and I'm not able to check it know, so feel free to correct me.

更简单的方法:在父表中添加一个名为 MaxChildId 的列，默认值为 0.每次添加子行时，您应该更新此列并使用其新的值作为 DisplayID.

Simpler method: add a column to parent table called MaxChildId with default value = 0. Each time you add a child row you should update this column and use its new value as the DisplayID.

declare @vid int

UPDATE [ParentTable]
SET @vid = MaxChildId+1, MaxChildId = MaxChildId+1
WHERE Id = ParentID
select @vid

此方法在更新父表时可能会导致并发.

This method could cause concurrences when updating parent table.

说到底，我认为你最好考虑重新设计来解决这个问题.

After all being said, I think you better consider a redesign for solving this problem.

这篇关于记录组的类似身份的自动递增 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！