Background: Rails project(recently updated from 4.2.1 to 4.2.5.2) with 267 migration files written over 6 years. This project maintains the migration of database which is used by 3 other Rails applications(where db tasks are disabled)
For setting up database and table in a new machine for development, we use the dump from production always. But I recently tried to run rake db:migrate and got this.
Mysql2::Error: Failed to open the referenced table 'referrer_organizations': ALTER TABLE `organization_referrals` ADD CONSTRAINT `fk_rails_aded077271`
The migration file that causes this issue is
class CreateOrganizationReferrals < ActiveRecord::Migration
def change
create_table :organization_referrals do |t|
t.references :referrer_organization, foreign_key: { to_table: :organizations }, index: true
t.references :referral_organization, foreign_key: { to_table: :organizations }, index: true
I had run rake db:migrate long time ago in 4.2.1 it had worked. Not sure why in 4.2.5.2 I'm getting this issue. Can it be due to 4.2.5.2?
So I did some search and though would use schema.rb to load the schema instead of relying on rake db:migrate. But a different error occurred.
ActiveRecord::StatementInvalid: Mysql2::Error: Duplicate foreign key constraint name 'fk_rails_0213c2b87e'
So I dug into the schema.rb and found out a duplicate lines.
add_foreign_key "jobspikr_data_queries", "jobspikr_organizations"
add_foreign_key "jobspikr_data_queries", "jobspikr_organizations"
Then I looked into the sql dump and found this.
CONSTRAINT `fk_rails_9b58d2d0ef` FOREIGN KEY (`jobspikr_organization_id`) REFERENCES `jobspikr_organizations` (`id`),
CONSTRAINT `fk_rails_f41d37972f` FOREIGN KEY (`jobspikr_organization_id`) REFERENCES `jobspikr_organizations` (`id`)
What could be the reason for this two constraint on the same column in the same table?
To overcome this issue I've created a new migration file as follows to remove one foreign key constraint
remove_foreign_key :jobspikr_data_queries, column: :jobspikr_organization_id
and ran it in my development machine, then took a dump of the schema, ran the new schema in test environment it worked fine.
Is it safe to remove a foreign key constraint as shown above in my production environment. Will there be any problem with data if I do this?