The default behavior in a Rails UI is to rely on numeric identifiers such that URLs (/myobject/kick/1) reference the database ID directly. The ID for myobject is 1.
This means that if you ever want to delete a record in your db, any URLs which reference that ID will get the Oops page. Now, granted if you are referencing a now deleted object somewhere, you’re going to have to deal with it somehow. Perhaps having the database IDs in the URL is a little close to the metal for some. Others like the simplicity and have no problem with it.
This practice, combined with some database defaults used in Rails, can lead to an issue however. MySQL migrations in Rails default to the InnoDB table type. This is probably all to the good, what with the support for transactions and all.
There is something that you may want to know about auto_increment id fields in InnoDB, however. On MySQL server startup, the InnoDB auto_increment value is grabbed from the database as the max value of the id column + 1. This value is stored in memory only and not persisted.
Taking a quick example…
If you have 10 objects in your database (ids 1-10) and you decide to delete them, your next ID will be 11. That is, unless, you restart your database server. An uncommon event for sure, but an eventual one indeed. When you restart the database, your auto_increment id is 1.
So, the /myobject/kick/1 now is not the same /myobject/kick/1 that was referenced before you decided to use those transactionally-safe InnoDB tables and restart your database.
About now, you might be wondering how to solve this and I think you have a couple of different options, if you really want to continue using auto_increment ids in this way.
You could take a look at Rick Olson’s acts_as_paranoid plugin and never actually delete your records. The plugin uses a deleted_at column to denote when something was “deleted” and it monkey patches Rails queries to ignore the records with a non-null deleted_at.
Otherwise, you’ll have to keep track of the real next auto_increment id value and manually set the value on the table.
ALTER TABLE myobjects AUTO_INCREMENT=11;
A more robust option would be to take ID generation out of the database altogether. This also has the benefit of deploying on a different database without worrying about the nuances of each database (or table type’s) behavior with respect to IDs.
Reference: How AUTO_INCREMENT Columns Work in InnoDB
