phpmyadmin Question posted in
The official documentation can be found here.

Auto-increment a primary key in MySql – Phpmyadmin

During the creation of tables using mysql on phpmyadmin, I always find an issue when it comes to primary keys and their auto-increments. When I insert lines into my table. The auto_increment works perfectly adding a value of 1 to each primary key on each new line. But when I delete a line for example a line where the primary key is ‘id = 4’ and I add a new line to the table. The primary key in the new line gets a value of ‘id = 5’ instead of ‘id = 4’. It acts like the old line was never deleted.

Here is an example of the SQL statement:

CREATE TABLE employe(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL
)

ENGINE = INNODB;

How do I find a solution to this problem ?

Thank you.

Tags:

3

Answers


  1. 0

    I’m pretty sure this is by design. If you had IDs up to 6 in your table and you deleted ID 2, would you want the next input to be an ID of 2? That doesn’t seem to follow the ACID properties. Also, if there was a dependence on that data, for example, if it was user data, and the ID determined user IDs, it would invalidate pre-existing information, since if user X was deleted and the same ID was assigned to user Y, that could cause integrity issues in dependent systems.

    Also, imagine a table with 50 billion rows. Should the table run an O(n) search for the smallest missing ID every time you’re trying to insert a new record? I can see that getting out of hand really quickly.

    Some links you might like to read:

    Login or Signup to reply.
  2. 0

    Why do you care?

    Primary keys are internal row identifiers that are not supposed to be sexy or good looking. As long as they are able identify each row uniquely, they serve their purpose.

    Now, if you care about its value, then you probably want to expose the primary key value somewhere, and that’s a big red flag. If you need an external, visible identifier, you can create a secondary column with any formatting sequence and values you want.

    As a side note, the term AUTO_INCREMENT is a bit misleading. It doesn’t really mean they increase one by one all the time. It just mean it will try to produce sequential numbers, as long as it is possible. In multi-threaded apps that’s usually not possible since batches or numbers are reserved per thread so the row insertion sequence may end actually not following the natural numbering. Row deletions have a similar effect, as well as INSERT with roll backs.

    Login or Signup to reply.
  3. 0

    Primary keys are meant to be used for joining tables together and
    indexing, they are not meant to be used for human usage. Reordering
    primary key columns could orphan data and wreck havoc to your queries.

    Tips: Add another column to your table and reorder that column to your will if needed (show that column to your user instead of the primary key).

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

Back To Top
Search