recreating and replacing database column values

Here's my question:

If I have a MySQL database column that reads:

index_no

1

2

3

4

5

and I delete one of the rows, let's say the one where index_no='2'....so now the column reads:

index_no

1

3

4

5

How do I recreate and replace the existing column values so that the column reads:

index_no

1

2

3

4

????

This database is ****, so I don;t have any other columns to cross-reference this column with....is there a way to simply recreate and replace the column values so they start at '1' (at the top) and (in increments of 1) go on to infinity (without cross-referencing any other columns, hopefully)?

Thanks again.

This is one way to do it, I don't know if there is a more efficient way but this works.

$query = "ALTER TABLE `table` DROP `index_no`;"; //delete id column
mysql_query($query); //run the query
$query2 = "ALTER TABLE `table` ADD `index_no` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;"; //add id column
mysql_query($query2); //run the query

Efficient enough for me, plus it works...thanks!

Also, methinks my SQL is not so good.....Do you mind explaining to me how this query works:

ALTER TABLE `table` ADD `index_no` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

thanks again... :thumbsup:

"ALTER TABLE `table`" //edit the table's structure (like SELECT but for editing)
"ADD `index_no`" //edit it by adding this column name
"INT" //column type (integer)
"NOT NULL" //doesn't allow it to be NULL (must have a value)
"AUTO_INCREMENT" //makes it an auto incrementing field (so when you add a column it makes it 1 higher than last entry
"PRIMARY KEY" //makes it the primary key for this table
"FIRST" //column position (so it's the first column because ids usually are

So basically the code i gave u gets rid of the column then places it back in and since it is an auto increment column it inserts new ids starting at 1 and incrementing by 1 each time.

Thanks, good to know what all that means.... :)