Auto Increment ID in SQL

Question

I want to know how can I make my SQL Database table give an ID starting from 0 and with every new add to check if there is an old ID free .

Exemple : I added 10 items then I deleted almost everyone and I kept item with ID=5. After adding a new item , it gets id 11 not 0.

Note : I'm editing my DB with Laravel API.

user_17873304 · Answer

SQL AUTO INCREMENT adds 1 to the biggest incrementable column value.

To fill a free integer slot you need to check for it manually, but that kinda defeats the purpose of auto-incrementing.

Anyway here is how to check for the first free ID value:

function getUnusedId($ids_array)
{
  $max_id = max($used_ids); // can be 25 for example

  // [1:(id|1), 2:(id|2), 3:(id|3), 4:(id|5)] =>> ID 4 is missing
  for($i = 1; $i <= $max_id; $i++){
    if($used_ids[$i] > $i){
      return $i;
    }
  }
  return $max_id + 1; // You can echo that all IDs are used.
}

// The IDs must be ordered for this solution
$used_ids = Model::orderBy('id')->pluck('id')->toArray();

$free_id = getUnusedId($used_ids);

Auto Increment ID in SQL

1 Answers