Trigger for re-setting auto-increment of tables in phpMyadmin
I have a database, tvguide and these are the values:
id bigint 255 UNSIGNED AUTO_INCREMENT programme varchar 255 default value (which is from another table with list of programs, the only linked field that is shared with other table):<b><a href="gossipgirl.php">Gossip Girl</a></b> channel varchar 255 airdate DATETIME - actual airtime displayair DATETIME (rounded-up datetime, e.g. programme that airs 1.59pm would be shown as 2.00pm, or 6.08pm as 6.10pm) expiration DATETIME - when program finishes episode VARCHAR 255 NULL - has null option if episode unknown series VARCHAR 255 epno VARCHAR 255 - episode number setreminder VARCHAR255 (shows "set reminder", sends email to user, but that's another thing to work on).
I want to insert a trigger into my database so that if a record is deleted, the ID auto-increment re-sets itself automatically?
Any ideas on how to accomplish this? I've done it manually so far but am looking at seeing what I can do with phpMyadmin when I delete the record.
BTW I made a copy of the table for backup purposes.
I agree with whitstone86's comment that you can just "let it grow".
Having said that, to change the AUTO_INCREMENT at any time, you can just run an ALTER TABLE.
Some pseudo code...
SELECT MAX(id)+1 AS max_id_plus_one FROM my_table; ALTER TABLE my_table AUTO_INCREMENT = $max_id_plus_one;
For production, race conditions might be an issue. However, if it's just you puttering around in phpmyadmin, this should suffice.