Re: Reset (or Defrag) the AUTO_INCREMENT columns

[Marco Simon]

Hi wolverine,

of course you could "defrag" your autoincrement-values, but there's
no automation for that - you've do do that via normal insert/update
statements.
Perhaps you'll need an intermediate table.

But:
In most cases the autoincrement-value is used as an id (as in your case)
- in
db-language it is often the (primary) key - which normaly is never ever
changed
through the live-time of a data-record. If you change your primary key
you'll
have to change all references to that key in your detail-tables.

Greetings,
  Marco

wolverine my schrieb:
> Hi!
>
> I have the following tables and the data,
>
> CREATE TABLE category (
>    id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
>    name VARCHAR(50) NOT NULL
> );
>
> CREATE TABLE user (
>    id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>    name VARCHAR(50) NOT NULL,
>    category TINYINT UNSIGNED REFERENCES category(id)
> );
>
>
> SELECT * FROM category;
> +----+------------+
> | id | name       |
> +----+------------+
> |  1 | Classic    |
> |  2 | Gold       |
> |  5 | Platinum   |
> |  6 | Blacklist  |
> +----+------------+
>
> SELECT * FROM user;
> +----+------------+----------+
> | id | name       | category |
> +----+------------+----------+
> |  2 | John       | 1        |
> |  3 | Mark       | 2        |
> |  5 | Kenneth    | 5        |
> |  6 | Sammy      | 6        |
> |  8 | Jane       | 5        |
> +----+------------+----------+
>
>
> Based on the above, the values of both ids are defragmented.
> The category.id 3 and 4 are deleted and
> the user.id 1, 4 and 7 are deleted.
>
> May I know if there is any way we can reset (or defrag?) the values so
> that they look like the following?
>
>
> SELECT * FROM category;
> +----+------------+
> | id | name       |
> +----+------------+
> |  1 | Classic    |
> |  2 | Gold       |
> |  3 | Platinum   |
> |  4 | Blacklist  |
> +----+------------+
>
> SELECT * FROM user;
> +----+------------+----------+
> | id | name       | category |
> +----+------------+----------+
> |  1 | John       | 1        |
> |  2 | Mark       | 2        |
> |  3 | Kenneth    | 3        |
> |  4 | Sammy      | 4        |
> |  5 | Jane       | 3        |
> +----+------------+----------+
>

smime.p7s
Description: S/MIME Cryptographic Signature