[ale] Auto increment in MySQL

[Michael B. Trausch]

George Carless wrote:
> 
> Do what?
> 
> Honestly, if you're worried about "filling the holes" then your table 
> scheme is poorly designed.  Rethink the index.
> 

If your index isn't related to anything else (although I can't think why
you would do that, unless this is an unimportant table that is related
to another "primary" table in the database), you can do what you're
asking by something similar to the following:

$query = "ALTER TABLE table_name "
       . "DROP col_name, "
       . "ADD col_name INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, "
       . "AUTO_INCREMENT = 1";
$res = @mysql_query($query, $conn);
if (!$res) { // handle errors }

Just dropping the column, and recreating it (after ensuring that your
table is in order by doing "ALTER TABLE table_name ORDER BY col_name")
will create the appropriate auto-increment field values that you need.

If this is a table that has it's ID field created with relationships to
other tables (enforced by the DB Server or not), be advised that this
may horribly break any applications that are making use of the database.
 The applications can't see what happened or why the relationships are
broken; and in fact, may not even know that the relationships are broken!

	- Mike
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 294 bytes
Desc: OpenPGP digital signature