MySQL + Rails: errno: 150 “Foreign key constraint is incorrectly formed”

Question

I am trying to migrate my Rails MySQL database and I am getting the following error:

ActiveRecord::StatementInvalid: Mysql2::Error: Can't create table development.comments (errno: 150 "Foreign key constraint is incorrectly formed"): CREATE TABLE comments (id int AUTO_INCREMENT PRIMARY KEY, comment varchar(255), user_id int, post_id int, created_at datetime NOT NULL, updated_at datetime NOT NULL, INDEX index_comments_on_user_id (user_id), INDEX index_comments_on_post_id (post_id), CONSTRAINT fk_rails_03de2dc08c FOREIGN KEY (user_id) REFERENCES users (id) , CONSTRAINT fk_rails_2fd19c0db7 FOREIGN KEY (post_id) REFERENCES posts (id) ) ENGINE=InnoDB

Here are my migrations:

class CreateUsers < ActiveRecord::Migration[5.0]
  def change
    create_table :users do |t|
      t.string :first_name
      t.string :last_name
      t.string :linkedin_username
      t.string :facebook_username
      t.string :facebook_id
      t.string :profile_image
      t.string :title_image
      t.string :connection
      t.boolean :team
      t.boolean :active
      t.boolean :admin
      t.string :email
      t.string :password_digest

      t.timestamps
    end
  end
end    

class CreateComments < ActiveRecord::Migration[5.0]
  def change
    create_table :comments do |t|
        t.string :comment
        t.references :user, foreign_key: true
        t.references :post, foreign_key: true
      t.timestamps
    end
  end
end

class CreateCategories < ActiveRecord::Migration[5.0]
  def change
    create_table :categories do |t|
        t.string :name
      t.text :description

      t.timestamps null: false
    end
  end
end

class CreatePosts < ActiveRecord::Migration[5.0]
  def change
    create_table :posts do |t|
        t.string :title
      t.string :description
      t.references :category, foreign_key: true
      t.references :user, foreign_key: true
    end
  end
end

I would be very thankful if someone could give me an idea here. I have been googling for solutions for a while now but could not find anything on this related to Rails. Thank you!

patrick patrick · Accepted Answer · 2016-08-29T07:51:09

I just found the answer to this question. My error was related to the fact that my posts table migration came last. My comments table migration was referencing the post table id parent field. As soon as I regenerated the comment table migration and deleted the previous comment table migration, everything worked out fine.

MySQL + Rails: errno: 150 “Foreign key constraint is incorrectly formed”

1 Answers