Question 20820 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL Workbench – indexes after delete (Java)

I have a problem with MYSQL Workbench. Let’s say I have this table, the user ‘x’ has index 8. Let’s say I delete this user, so now I have only 7 users. If I create a new user with Java Console, the new user will be created with ID 9 because it’s in auto increment. Is there a way to tell Workbench to start from where it is the last number? in that specific case, start creating the new user from 8 and not 9?

I tried to check everywhere on the web, but found only some query to recreate the table, but its not what I want because I have some foreign keys linked to that table, so I can’t just delete the table and recreate another one.

Tags:

2

Answers


  1. 0

    I’m not familiar with MYSQL Workbench, but I think you might be able to alter your table and to change the value of the AUTO_INCREMENT.

    Here’s a link that talk about it in more details and even has an example:https://www.w3schools.com/mysql/mysql_autoincrement.asp

    I hope this help you.

    Login or Signup to reply.
  2. 0

    There is not a way to do that. Using Auto Increment or Identity makes a value no longer usable once it is used.

    So a user with an ID of 1 will always have an ID of 1. A possible workaround is to not delete users, but instead put in a column of TinyInt(1) labeled ‘Deleted’

    New Users will have Deleted set to 0. When a user is deleted, their record gets set to 1. It will keep your IDs in order and then you just need to account for querying users who are not deleted.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search