Improper incrementing of User ID

Question

Possible Duplicate:
MySQL: Reorder/Reset auto increment primary key?

I've created a file called register.php which logs each user's User ID. My partner has created his own version. Inside our databases, I am User ID 1 by default, and he is User ID 2. We both have 4 additional users. The issue I'm having is that I deleted all users from my database, but whenever a new member registers to my site, their User ID is either 7 and up - instead of 2 (which is right after mine). Can anybody help me to solve this?

My shortened Database:

    CREATE TABLE `users` (
   `user_id` int(100) not null auto_increment,
   `user_name` varchar(50) not null,
   `user_pass` varchar(100) not null,
   `user_email` varchar(255) not null,
   PRIMARY KEY (`user_id`),
   UNIQUE KEY (`user_name`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8_unicode_ci AUTO_INCREMENT=8;

Now here is the Sql:

$sql = "INSERT INTO
users(user_name, user_pass, user_email , user_date , user_level)
VALUES('" . mysql_real_escape_string($_POST['user_name']) . "',
'" . $ip . "',
'" . sha1($_POST['user_pass']) . "',
'" . mysql_real_escape_string($_POST['user_email']) . "',
NOW(),
0)";

What needs to be solved? If there where 6 users and got deleted, the next id given is 7. — JvdBerg, Oct 24 '12 at 18:33
possible duplicate of [MySQL: Reorder/Reset auto increment primary key?](http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key) and http://stackoverflow.com/questions/8923114/mysql-reset-auto-increment — j08691, Oct 24 '12 at 18:35
To elaborate on @JvdBerg's comment, by default Mysql does not re-use auto increment primary keys. That's why it starts again at 7, because 1-6 have already been used, even if records 2-6 no longer exist. — Mansfield, Oct 24 '12 at 18:37
It is good practice not to reuse user IDs of deleted users. If you have a normalized table, you will be referencing a lot of other tables using user ID as a foreign key. — Lord Loh., Oct 24 '12 at 18:38

score 1 · Answer 1 · answered Oct 24 '12 at 18:34

1

You have to reset the auto increment on the table, so it generates the lower numbered User IDs after you delete them.

ALTER TABLE mytable AUTO_INCREMENT = 3

http://www.electrictoolbox.com/reset-auto-increment-value-mysql/

answered Oct 24 '12 at 18:34

Louis Ricci

20,804
5
48
62

Thanks guys. It took awhile, but I had to use what you guys told me to organize and fix the areas that were incorrect. Big thanks to everyone. – Evylent Oct 24 '12 at 20:04

score 1 · Answer 2 · answered Oct 24 '12 at 18:37

When you define your table you are declaring the following field to auto-increment:

`user_id` int(100) not null auto_increment,

What this means is that the database will keep an independent value for the last increment it made. This ensures that each new row has a unique user_id.

You can reset it to some number using the following SQL:

ALTER TABLE mytable AUTO_INCREMENT = {the number you want};

However, this will cause problems if you delete some but not all users. If you have users 1-7, delete user id 5, and reset the auto increment to 5, and add three users, you'll have users 1-7, but you'll have duplicates of 6 and 7. This is not good for a unique index.

Generally unique ids are NOT user facing, so the actual value doesn't matter. In this solution you give the user an alternative, user facing identifier. This can be a hashed number, a username or something else entirely (like a picture). The user id is left to simply be a hook to maintain data integrity.

An alternative method is that when you insert a new user you scan through the database for the first unused number. This is computationally expensive though, and the benefit is unclear.

For the cleanest code, decide precisely what the user_id should represent and use it for that alone. Then build your program to represent that abstraction.

Improper incrementing of User ID

2 Answers2