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 |
+----+------------+----------+
