List:General Discussion« Previous MessageNext Message »
From:wolverine my Date:June 14 2006 2:09am
Subject:Reset (or Defrag) the AUTO_INCREMENT columns
View as plain text  
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        |
+----+------------+----------+
Thread
Reset (or Defrag) the AUTO_INCREMENT columnswolverine my14 Jun
  • Re: Reset (or Defrag) the AUTO_INCREMENT columnsDaniel Kasak14 Jun
    • Re: Reset (or Defrag) the AUTO_INCREMENT columnswolverine my14 Jun
      • Re: Reset (or Defrag) the AUTO_INCREMENT columnsDaniel Kasak14 Jun
        • RE: Reset (or Defrag) the AUTO_INCREMENT columnsDaevid Vincent14 Jun
  • Re: Reset (or Defrag) the AUTO_INCREMENT columnsMarco Simon14 Jun