Duplicate foreign key constraint caused by two constraints on the same column

Viewed 247

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?

0 Answers
Related