Considerations for using attribute rule to generate sequential IDs in existing ASSET_ID field

[Bud]

ArcGIS Pro 3.4.3; Oracle 19c 11.3 EGDB; Traditional versioning moving edits to base.

My organization has recently upgraded from Pro 2.9.5 (had attribute rule limitations) to 3.4.3. And ArcMap has been phased out. So now, we are taking the plunge into calculation attribute rules.

We're planning to use attribute rules to automatically generate IDs in ASSET_ID fields in various existing FCs.

Question:

What do we need to consider when using an attribute rule to generate unique IDs for new rows in an existing FC? We may want to use use the attribute rule to enforce data integrity in general as well.

1. How to start/continue the sequence at the largest existing number in the field?
2. How to pad the numbers with zeros?
3. Should the field allow nulls?
4. Should the field be manually editable? Or should we disallow edits? Consider data dependencies/syncs to external systems like a work order management system.
5. We could check to make sure there is a unique database index that enforces unique IDs. Or alternatively, switch from a database index to an attribute rule for this, since the database index causes issues when splitting linear assets.
6. Consider relationship classes to other FCs or tables. I don't think we have any for our asset FCs, but it's still good to think about this.
7. Should we prevent row deletions, for the purpose of integrations to external systems?

Is there anything else we should consider?

[DavidSolari]

Answering off the top of my head:

1. You can pick the starting number when you create the sequence. If you're using a sequence then assume each sequence pull will generate a new largest number, if you have to check the table every time to find the previous largest number then you'll run into performance issues, and at that point you don't need a sequence to bump a number up by 1 anyways.
2. The Arcade Text function has a second parameter for number formatting that includes 0 padding.
3. Non-null fields are always preferable for record IDs, just make sure it has a sensible default as fields with calculation rules are inserted in their initial state before the rule updates things
4. Disallowing edits through the calculation rule is the safest move, otherwise it's trivial to break the identity. Worst case you can hop into the database and tweak an ID without ArcGIS.
5. You should be able to pop a unique index on the field straight through Pro's Manage dialog. Worst case you add a non-unique index which will still be a big performance boost if you need to query by ID.
6. No issues with relationship classes, I have a 1-m relationship where the parent uses unique IDs and there's been no issues on that front.
7. There's no special consideration with sequences and deleting rows, just keep in mind it'll lead to fragmented IDs over time.

One last tip: try adding a short prefix to the IDs like "DB" or "GIS" or something, that way if you need to work with IDs that come from other sources you don't have to worry about numeric collisions.