Hello, Normally, we create databases from CDTs and Datastores, and using au

Adel Ammari over 9 years ago

Hello,
Normally, we create databases from CDTs and Datastores, and using auto generation for primary keys.

After using whichever application we develop which saves on that table, the primary key count reaches a certain number per say 20. At some point, we would like to drop all data and start clean, resetting the primary key counter back to 1.

To overcome this, we tried dropping and recreating the sequences, which seems to work well, but not for long, after resetting the counter 1, and using the system for a couple of days, we find a sudden jump, causing a non consistent primary key counter. e.g.1.2.3.4 then 21 instead of 5.

Please advise what else needs to be done to fully reset the counter to 1 causing a clean consistent primary key count 1.2.3.4.5...etc.

Discussion posts and replies are publicly visible

Parents

0 Sathya Srinivasan
Appian Employee
over 9 years ago

What you are describing is common in databases like ORACLE. So first thing would be to understand your database and how primary keys are managed. For e.g. in MySQL, its an out of the box database feature to manage auto increment on a primary key field - However, in Oracle, it is done by defining a sequence. Now, sequences have a different mechanism based on how you want to block sequencing. Oracle sequences have caching that blocks a set of numbers assuming an optimistic sequencing. So if another call comes in at around the time, the sequence number will jump by the cache size. Caches are meant for improving write efficiency in Oracle. You might want to look into this.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Sathya Srinivasan
Appian Employee
over 9 years ago

What you are describing is common in databases like ORACLE. So first thing would be to understand your database and how primary keys are managed. For e.g. in MySQL, its an out of the box database feature to manage auto increment on a primary key field - However, in Oracle, it is done by defining a sequence. Now, sequences have a different mechanism based on how you want to block sequencing. Oracle sequences have caching that blocks a set of numbers assuming an optimistic sequencing. So if another call comes in at around the time, the sequence number will jump by the cache size. Caches are meant for improving write efficiency in Oracle. You might want to look into this.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data