CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4migration
Aniket Tiwari
Aniket Tiwari

Reputation: 4008

Getting error when running migrations

When I am running migration I am getting an error .I have tried removing primary key column but nothing work 

  Mysql2::Error: Can't create table 'new_gquestion.#sql-408_33' (errno: 150): ALTER TABLE `retailers` ADD CONSTRAINT `fk_rails_caa912f3c3`
FOREIGN KEY (`location_code`)
  REFERENCES `zusers` (`id`)
/home/techbirds/new-gquest/path/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `_query'
/home/techbirds/new-gquest/path/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `block in query'
/home/techbirds/new-gquest/path/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `handle_interrupt'
/home/techbirds/new-gquest/path/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `query'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `block in execute'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `block in log'
/home/techbirds/new-gquest/path/gems/activesupport-4.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:467:in `log'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `execute'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:231:in `execute'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/schema_statements.rb:748:in `add_foreign_key'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:662:in `block in method_missing'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:632:in `block in say_with_time'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:632:in `say_with_time'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:652:in `method_missing'
/home/techbirds/new-gquest/db/migrate/20160607052319_add_location_code_into_retailers.rb:5:in `change'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:606:in `exec_migration'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:590:in `block (2 levels) in migrate'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:589:in `block in migrate'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:588:in `migrate'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:765:in `migrate'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:995:in `block in execute_migration_in_transaction'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:1043:in `ddl_transaction'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:994:in `execute_migration_in_transaction'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:956:in `block in migrate'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:952:in `each'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:952:in `migrate'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:820:in `up'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/migration.rb:798:in `migrate'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/tasks/database_tasks.rb:137:in `migrate'
/home/techbirds/new-gquest/path/gems/activerecord-4.2.1/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top (required)>'
/home/techbirds/.rvm/gems/ruby-2.2.1/bin/ruby_executable_hooks:15:in `eval'
/home/techbirds/.rvm/gems/ruby-2.2.1/bin/ruby_executable_hooks:15:in `<main>'
ActiveRecord::StatementInvalid: Mysql2::Error: Can't create table 'new_gquestion.#sql-408_33' (errno: 150): ALTER TABLE `retailers` ADD CONSTRAINT `fk_rails_caa912f3c3`
FOREIGN KEY (`location_code`)
  REFERENCES `zusers` (`id`)



def change
    add_column :retailers,:location_code,:string
    add_index :zusers,:location_code
    add_foreign_key :retailers,:zusers,column: :location_code, primary_key: "location_code"
  end

Upvotes: 0

Views: 159

Answers (1)

Larry Lv
Larry Lv

Reputation: 769

I setup a new Rails codebase to test your migration, and it worked fine.

See the repo here and the migration here

Mysql2::Error: Can't create table 'new_gquestion.#sql-408_33' (errno: 150): ALTER TABLE retailers ADD CONSTRAINT fk_rails_caa912f3c3 FOREIGN KEY (location_code)
REFERENCES zusers (id)

I think this error happens when you removed primary key column specification, and it didn't work because location_code is string type and id is integer. (When you didn't specify the primary_key option, id is default column.)

The reason why you specified the primary_key option and it didn't work, I'm not sure. I suggest you check if you have the location_code column in you zusers table.

You could checkout my sample repo commits https://github.com/larrylv/sf-37782027/commits/master, just creating these two tables and using your migration script to add the foreign key.

Upvotes: 3

Related Questions