Running remove_column not working in rails migration

Question

I have a very simple migration:

class RemoveAuthorIdFromBooks < ActiveRecord::Migration
  def change
    remove_column :books, :author_id
  end

end

But I get the following error:

Mysql2::Error: Error on rename of './mysite_staging/#sql-3b1_3c78' to './mysite_staging/books' (errno: 150): ALTER TABLE `books` DROP `author_id`

This is the description of the table:

+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| id               | int(11)       | NO   | PRI | NULL    | auto_increment |
| author_id        | int(11)       | NO   | MUL | NULL    |                |
| title            | varchar(255)  | NO   |     | NULL    |                |
| teaser           | varchar(500)  | NO   |     | NULL    |                |
| description      | varchar(2000) | YES  |     | NULL    |                |
| cover_image      | varchar(255)  | NO   |     | NULL    |                |
| publication_date | date          | NO   |     | NULL    |                |
| enabled          | tinyint(1)    | NO   |     | 1       |                |
| created_at       | datetime      | NO   |     | NULL    |                |
| updated_at       | datetime      | NO   |     | NULL    |                |
| excerpt          | text          | YES  |     | NULL    |                |
| featured         | tinyint(1)    | YES  |     | NULL    |                |
| site_id          | int(11)       | YES  |     | NULL    |                |
+------------------+---------------+------+-----+---------+----------------+

Any clues?

What does your current table looks like? ('rails db' and then describe books;). Also, what versions are you using(Rails, Mysql2. OS?) — Novae, Feb 14 '13 at 20:02
Possibly related to http://stackoverflow.com/q/1451042/684934 — , Feb 14 '13 at 20:13
This might have to do with the fact that the author_id is an index. Depending on what SQL you're using and how it's configured it might be a problem. try removing the index before removing the column. (remove_index :books, :author_id) — Novae, Feb 14 '13 at 20:15
Index name 'index_books_on_author_id' on table 'books' does not exist — Tony, Feb 14 '13 at 20:19

score 6 · Answer 1 · answered Feb 19 '16 at 07:12

6

If anyone still facing this with Rails 4 and above, then you could do the following

remove_reference(:books, :author, index: true, foreign_key: true)

answered Feb 19 '16 at 07:12

karthik c

61
1
3

score 1 · Accepted Answer · answered Feb 14 '13 at 20:41

1

For some reason a foreign key constraint was breaking the drop sentence.

I did the following:

show create table books;

Looked at the foreign key name and then:

alter table books drop foreign key books_ibfk_1;

Then rake db:migrate worked.

answered Feb 14 '13 at 20:41

Tony

10,088
20
85
139

Running remove_column not working in rails migration

2 Answers2