Maintaining A Secondary, Relative ID Column

Hey, I'm looking for t开发者_运维百科he most efficient way to maintain an object relative auto-incrementing value. In other words, I have a table of objects, and a table of child objects. The child objects need to have a column, which increments relative to the other child objects for the same parent. (ie. child object 3 of parent object 4)

eg.

Parent Objects:
ID|name
1|Object1
2|Object2
3|Object3

Child Objects:
ID|relativeID|parentObjectID|name
1|1|1|Some Child Object
2|1|2|Some Child Object
3|2|1|Some Child Object
4|2|2|Some Child Object
4|3|1|Some Child Object

What's the most efficient way to generate the relativeID column? I'm thinking I should I query for the MAX(relativeID) with the same parentObjectID, then do a the insert, but I'm wondering if that would cause issues if there were concurrent inserts on the same parent object. Is there a better way to approach this?

Add a field to the parent objects table specifying the max number of child sub-id. When you are inserting, make sure to do it in a transaction, so that no one can get in between your reading the max sub-id, and your modifying it.

You have missed stating the Database you are using.

What you could attempt is to retrieve the MAX relative id per parent object and use that in a transaction

This will give you the MAX id per parent object

DECLARE @Table TABLE(
        ID INT,
        ID_RELATIVE INT,
        PARENT_OBJECT_ID INT
)

INSERT INTO @Table SELECT 1,1, 1
INSERT INTO @Table SELECT 2,1, 2
INSERT INTO @Table SELECT 3,2, 1
INSERT INTO @Table SELECT 4,2, 2
INSERT INTO @Table SELECT 4,3, 1

SELECT  MAX(ID_RELATIVE) LAST_ID_RELATIVE,
        PARENT_OBJECT_ID
FROM    @Table
GROUP BY PARENT_OBJECT_ID

Now you can limit this using the parent object id, and only retrieve the MAX id for a given parent objectid. Then you can just add 1 to that MAX found and use that as a new autonumber.

Please let me know if you require some more assistance, or if this does not work in your specific DB.