2
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int color_id;

the above generates a automatic incremented number like 1,2,3,4,5,6. But when we delete record with 6 , the next generated number is 7. but i want next generated number to be 6. what should i do? i need auto increment to continue from 6,7 itself not 7,8 is there any way?

TEDDY
  • 821
  • 10
  • 24
  • You need to drop that auto generated column & have to add that column again with auto generate constraint. This is the only way to solve this problem. – Vighanesh Gursale Oct 30 '14 at 11:28
  • @Vighanesh Gursale how to drop that column , when an row is deleted, can you explain it ?. please. – TEDDY Oct 30 '14 at 11:37
  • 1
    you didn't understand what I am trying to say. Check this link http://stackoverflow.com/questions/9754244/remove-gaps-in-auto-increment – Vighanesh Gursale Oct 30 '14 at 11:54
  • 1
    You're thinking not deep enough about your problem either. Say you have colors 1,2,3,4,5,6 and you delete the color with ID 5 - then all of a sudden you have colors 1,2,3,4,6. If you would then let auto_increment solve your problem for you, it would insert a color with ID 5 in there. You don't want the database to automagically do the ordering for you - you want an order column and write application business logic which properly maintains the ordering in all possible situations. – Gimby Oct 30 '14 at 12:27
  • @Gimby NO, colors 1,2,3,4,5,6 and you delete the color with ID 5 and add another new entry the color id generated is 6, but i want it to be 5 itself. – TEDDY Oct 31 '14 at 04:21
  • It will be 7 actually as 6 already exists, I was speaking hypothetically. At this point you need to start realizing that you do NOT want this. – Gimby Oct 31 '14 at 08:18
  • thnak you ...@Gimby ,@Vighanesh Gursale – TEDDY Oct 31 '14 at 11:05

1 Answers1

4

What autogeneration guarantee is only unicity of the key. And that's all you should need. There are even cases where autogeneration gives a bunch of keys to improve efficiency with shared databases :

  • a client asks to create a new row
  • a consecutive serie of id is reserved for that client connection
  • this client may create many new lines without re-asking database for new ids

This case will almost always generate row id that

  • contains hole
  • id are not necessarily incremented following time of insertion

Trust me : some production systems are glad with it because it causes less contention on network and database.

You are on your own if you want strictly consecutive id with no hole, but do not ask automatic database id generation to do it for you.

Serge Ballesta
  • 143,923
  • 11
  • 122
  • 252