question about auto incrementing.

tommychi

I currently have a users database for all the users that register and whatnot. Each user has a unique id and it auto increments. However, im just wondering, how would i have it so if i delete a random user, the userid minuses 1. By doing this, it wouldn't randomly have a break in the userid. For example:

i have 5 users with userids:
1, 2, 3, 4, 5.

i for some reason delete user 3, so now its:
1, 2, 4, 5.

if someone else were to register, it would now display:
1, 2, 4, 5, 6.

i want it to be just normal like this:
1, 2, 3, 4, 5

even if someone else were to register although there was a gap in between the userids cuz of a deletion. Hope someone can help.

drawmack

You would have to use pseudo auto_numbering for this. That means you manage it in the php instead of in the dbms. It'll be a lot slower and have almost no gain.

ultraslacker

I would say no gain - you want uniqueness in your keys, that's it. The sequence or lack of should not affect the application. If it does, rethink the design.

jim_keller

You probably don't want to do this...but if you really want to, I would write a function that goes through the DB looking for a missing index number. Something like this:


function get_unused_uid() {
  //Connect to the db here.
  $open_id = 0;
  $ordered_id = 1;
  $sql_query = "SELECT myID FROM myTable ORDER by myID";
  $result = mysql_query($sql_query);

  while ( $row = mysql_fetch_array($result) ) { 
      $cur_id = $row['myID'];

  if ( $cur_id > $ordered_id ){    
     break;
  }

  $ordered_id++;

  }
  $open_id = $ordered_id;

  return $open_id;
}

$open_id will contain the index of the next unused id field. Again, though, this probably isn't something you want to do. It's rather inefficient, and user ID's (or any auto_increment field, really) shouldn't be recycled; it can cause all kinds of confusion in your code and/or database design.

-Jim Keller

drawmack

Originally posted by ultraslacker
I would say no gain - you want uniqueness in your keys, that's it. The sequence or lack of should not affect the application. If it does, rethink the design.

There is one benefit, you won't run out of ids as quickly but with using a bigint field you get a rediculous number of ids to begin with and if you're all that worried about running out then create two fields for the primary key and use them like an odometer.