Reassigning SQL Auto_Increment ID Values in Rails

I typically like to assign shared select items with negative ID values.  This post describes using negative ID values for a selection of stores or coupon types, where “Manufacturer’s Coupon” and “Other” have negative values.  Here’s a screenshot from that post.

We’re going to want our Stores SQL table to include -1: Manufacturer and -2: Other records which brings us to the subject at hand, reassigning SQL Auto_Increment ID Values in Rails.  When I searched on this the typical response was something along the lines “run rails script/dbconsole and change the records directly.” Technically that answer is correct, but this post will demonstrate walking through the process.

In Development

In Development we’re probably using Sqlite3.  We’ll edit our application SQL tables by entering “rails dbconsole” at the root of our Rails app.

For our demonstration we’re going to change an Auto_Increment field in a scratch app’s Users table, changing user with ID “70” to and ID of “-1.”  We’re in dbconsole and will edit the existing record’s ID with a SQL statement.  For those of us coming out of the SQL Server world, we don’t have to set AutoIncrement Off or anything else in Sqlite, PostgreSQL or MySql. Just update the record.

Done!  Here’s the result.

On Heroku

What about on a public-facing site like on Heroku.  Heroku uses PostgreSQL, so we’ll need to install PostgreSQL on our Linux Mint box (what, you’re not running Linux Mint? :)  That gives us the ability to use pg:psql to update our application database on Heroku as easily as we updated our local Sqlite database earlier.

$ heroku pg:psql

We’ll do a direct sql update and view our results. Bam!  Auto_Increment ID values updated!