I was trying out posting a new acronym based on the final version of source code for chapter 9 (Parent Child Relationships).
I reset the database before adding a new acronym, but I failed to create a user before doing a call to create the acronym. This returns an HTTP 500 error for the call to create an acronym.
Following that, I successfully added a user, then create an acronym with the newly created user’s id. This returns an HTTP 200 and the acronym is created successfully.
But I notice that the id of the acronym is 2 and not 1. Is there a reason why a failed attempt at creating a new record increments the index of the table? Or is this a bug somewhere?
Hmm I guess it depends on the DB implementation. What’s probably happened is it created the acronym but that failed since the user key constraint was invalid so it rolled it back and deleted it. However, since it already was in the DB, the ID counter had been incremented. It does depend on the DB though
It’s the database itself that decides it. As for whether it’s intended behaviour my guess would be yes, but you’d need to check with the database itself
In any multi-user database that supports transactions, it has to be this way.
User A can start a transaction and get ID 1, then user B starts a transaction and gets ID 2, then user C starts one and gets ID 3. Maybe user A’s transaction is rolled back because of an error, but it is too late to reuse ID 1, and we don’t know about 2 or 3 yet, and A wants another ID now, so they are going to get 4, whether they like it or not!
Also note that records 2, 3, and 4 may be committed in the order 3, 4, 2, depending on what order the users commit in. So record 2 can end up being the newest record!
You can’t count on them to be perfectly sequential or in order of addition. Be happy that they are unique.