AUTO_INCREMENT MySQL

There is a bug in MySQL that reset AUTO_INCREMENT if an index is added and the table is empty. I found a ticket regarding this issue in Ruby on Rails' bug tracker but it won't be fix because it is a MySQL bug.

Anyway, I reset AUTO_INCREMENT to my desire value after adding indexes to the table and that seems to work. Just remember to not add anymore indexes to the table after you set the AUTO_INCREMENT value or else it will be reset again.

Here is how to set the value manually.

execute "ALTER TABLE table1 AUTO_INCREMENT = 100"


added method to help create and drop foreign key

We have a naming convention at my work place for foreign key so I made two methods to help in the creation and dropping of foreign key. I made them because I got tired of typing out the query everytime.

We name our foreign key like this: fk_table_column_reference_table_column. So a foreign key from a "users" table to the "employers" table would look like this. fk_users_employer_id.

This is how you call the method to create a foreign key.


create_foreign_key(:users, :employer_id, :employers, :id)
drop_foreign_key(:users, :employer_id)



def create_foreign_key(table, column, foreign_table, foreign_column)
execute "ALTER TABLE #{table.to_s} ADD CONSTRAINT fk_#{table.to_s}_#{column.to_s} FOREIGN KEY (#{column.to_s}) REFERENCES #{foreign_table}(#{foreign_column})"
end

def drop_foreign_key(table, column)
execute "ALTER TABLE #{table.to_s} DROP FOREIGN KEY fk_#{table.to_s}_#{column.to_s}"
end