Search code examples
mysqlruby-on-railsrubysequel

How to add UNIQUE AUTO_INCREMENT FIRST id column

I would like a database migration that is basically the following SQL:

ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST;

Is there a Sequel migration that does exactly this? I have gotten pretty close with the following, but it doesn't seem to be exactly what I want:

Sequel.migration do
  change do
    alter_table :my_table do
      add_column :id, Bignum, null: false, unique: true
    end
  end
end

Specifically, it seems to be missing auto-increment and won't be the first column.

Solution

  • This should work:

    DB.add_column :myTable, :id, Bignum, null: false, unique: true, :auto_increment=>true

    Note that that doesn't give you FIRST. If you want FIRST, you'll have to use raw SQL:

    DB.run "ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST"

    The other difference is your SQL doesn't specify NOT NULL, but your Sequel code does.