Database Sequence Number Reuse

1025
3
Jump to solution
10-11-2022 03:23 PM
GarretGiordanoTOLE
Occasional Contributor

My question is a bit a of a two fold situation. I am looking into creating a database sequence for feature in our Enterprise database for creating unique IDs.

I can't find any documentation that addresses the following. If a number is used for a feature in a class and then that feature is deleted, when a new feature is inserted does that number get reused or is it burned, and the next unused number comes up?

My second question is, can the sequence be used in 'Calculate Field'? I would use this in the attribute rules, but the data is existing, and I would need to run it for the first time manually. 

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
MVP Frequent Contributor

If a number is used for a feature in a class and then that feature is deleted, when a new feature is inserted does that number get reused or is it burned, and the next unused number comes up?

The database sequence doesn't know and doesn't care about where its values are used. All it does is count up when you say so. So when you delete and then insert a feature, the sequence will count up.

Just to clarify: The number isn't "burned", and the next number isn't "unused". These concepts would imply that the sequence has knowledge of where the values are used. It does not have that knowledge. It counts up.

 

can the sequence be used in 'Calculate Field'?

Yes.

 

Personally, my unique key rules look like this:

// Calculation rule on the key field
// triggers: Insert, Update

// if the key field is empty, get the next value of the sequence
// else return what's already in there
return IIF($feature.KeyField == null, NextSequenceValue("SequenceName"), $feature.KeyField)

 


Have a great day!
Johannes

View solution in original post

3 Replies
JohannesLindner
MVP Frequent Contributor

If a number is used for a feature in a class and then that feature is deleted, when a new feature is inserted does that number get reused or is it burned, and the next unused number comes up?

The database sequence doesn't know and doesn't care about where its values are used. All it does is count up when you say so. So when you delete and then insert a feature, the sequence will count up.

Just to clarify: The number isn't "burned", and the next number isn't "unused". These concepts would imply that the sequence has knowledge of where the values are used. It does not have that knowledge. It counts up.

 

can the sequence be used in 'Calculate Field'?

Yes.

 

Personally, my unique key rules look like this:

// Calculation rule on the key field
// triggers: Insert, Update

// if the key field is empty, get the next value of the sequence
// else return what's already in there
return IIF($feature.KeyField == null, NextSequenceValue("SequenceName"), $feature.KeyField)

 


Have a great day!
Johannes
GarretGiordanoTOLE
Occasional Contributor

Thank you for your response! As a point of clarification, lets say we have 5 features in a class ID's assigned are 1,2,3,4,5. We delete feature 3 and then insert a new feature. That would mean our 5 remaining features would be numbered 1, 2, 4, 5, 6 correct?

0 Kudos
JohannesLindner
MVP Frequent Contributor

Correct.


Have a great day!
Johannes