1

I just answered this question: Can I get the ID of the data that I just inserted?. I explained that in order to know the ID of the last record inserted in a table, what I would do is inserting it manually, instead of using some sequence or serial field.

What I like to do is to run a Max(id) query before INSERT, add 1 to that result, and use that number as ID for the record I'm about to insert.

Now, what I would like to ask: is this a good idea? Can it give some trouble? What are the reasons to use automatically set field on IDs fields?

Note: this is not exactly a question, but looking help center it seems like a good question to ask. If you find it to be off-topic, please tell me and I'll remove it.

CR0N0S.LXIII
  • 349
  • 2
  • 11
  • 1
    Running a `max(id)` is a really bad idea. This won't work if you have concurrent transactions (because all see the same value for the max()) - plus it's horribly inefficient. Using a sequence or an identity column is the most efficient and scalable solution. –  Nov 06 '19 at 11:29

2 Answers2

1

This is a bad idea and it will fail in a multi threaded (or multi users) environment.

Please note that the surrogate-key vs natural-key debate is still far from having a concrete definitive solution - but putting that aside for a minute - even if you do go with a surrogate key - you should never try to manually auto-increment columns. Let the database do that for you and avoid all kinds of problems that can occur if you try to do that manually - such as primary key constraint violations in the best case, or duplicate values in the worst case.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

If an Entity uses an ID as the Primary-Key, it is in general a good idea to let the DB autocreate it, so you don't need to determine an unused one while creating this Entity in your code. Furthermore a DateAccessObject(DAO) does not need to operate on the ID. Dependant on what DB u might use, you might even not be allowed to retrieve all IDs of that Table..

I guess there might be other good reasons to let the DB manage this part.

Lecagy
  • 489
  • 2
  • 10