Update serial ID when a table is NULL

Asked

Viewed 107 times

-1

I’m making a java desktop program, with a product ID automatically incremented by SERIAL.

However, when I delete some element or all, the value of the sequence stays on the last one that was added on, and I have to go to the bank and change that value by hand.

Does anyone have an idea of a Rigger or if I can change this in the settings of the sequence itself?

java postgresql
  • 1

    Because you need to reset the serial?

  • Because when I delete something from the table it doesn’t update the id.

  • Doesn’t update the id, would that be what exactly? what would you expect?

  • I delete a product with id 1, the next product I register in the system gets id 2. The same thing if I delete a table with 600 products, if I register a new product it starts with 601.

  • 3

    This is the expected behavior, you do not 'reuse' ids, either because there are related records in other tables and mainly the effort to reorder all records.

  • Related: http://answall.com/q/78544/3084

  • 1

    I share what @rray said and reinforce that trying to repurpose Ids is something that requires a completely unnecessary effort.

  • 1

    What is expected behavior if you have ids, 1 to 10 and you delete 7? register the new as 7, or readjust all of the following for the next one to be 10?

  • 1

    This is a situation that almost every database beginner goes through, thinking that they’re missing something by "wasting" serial/autoincrement Ids and that repurposing it will bring some benefit. @Joana unless you have a problem really Specifically this repurposing is a bad idea, if you have please add on the question because I have honestly never seen one and am curious ;p

Show 4 more comments

2 answers

3


This is the expected behavior of a DB auto increment field, it does not make much sense to search for a deleted ID and force the DB to add this ID in the new registration, you will lose a lot of performance because imagine your table with 2000 items or more, this is not a good practice.

  • I got it. I hadn’t really looked at it from that angle, I was just worried about the values I’d throw away. I am still beginner and it is always good to improve programming practices. Thank you!

  • Magina, we are here to help each other, because everyone here once was already beginner.

0

  • I already did that! I was wondering if there is a way to identify the lack of a product with a certain id and not leave this value empty.

  • 1

    ;) you may have even done but did not quote this in the question, I cannot guess, so it is important to quote the (some) attempts (even if wrong) to solve the problem, so everyone what not to do.

  • "...However, when I delete some element or all, the value of the sequence stays on the last one that was added on, and I have to go down to the bank and change that value by hand." I thought I made myself clear.

  • 1

    @Joana, I also don’t understand exactly what you want, but if what you want is to reuse Ids that were previously used and then deleted, forget this idea, this is recipe for problem. Ids were made for this very... let increase the will, if there are "holes", do not worry, it makes no difference.

  • @Joana you can create a method called resetSerial(String tabela) where you call this command. Or if you want something more automated you can list all sequences in checkbox and mark which ones you want to reset the value.

Browser other questions tagged java postgresql

You are not signed in. Login or sign up in order to post.