Updating existing column in Rails table to be foreign key to another table

Question

I have an existing table in rails called Users. It has a column called gender. I have another table called GenderOptions. I want to create a migration so that the gender column in the Users table is a foreign key referencing the GenderOptions table.

the first attempt migration file looks like this:

class ForeignKey < ActiveRecord::Migration[7.1]
  def change
    add_reference :users, :gender, foreign_key: {to_table: :GenderOptions}
  end
end

But I get an error message:

 add_reference(:users, :gender, {:foreign_key=>{:to_table=>:GenderOptions}})
rake aborted!
StandardError: An error has occurred, all later migrations canceled: (StandardError)

Mysql2::Error: Duplicate column name 'gender_id'
...:in `change'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Duplicate column name 'gender_id' (ActiveRecord::StatementInvalid)
...:in `change'

Caused by:
Mysql2::Error: Duplicate column name 'gender_id' (Mysql2::Error)
...:in `change'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

I also tried:

class ForeignKey < ActiveRecord::Migration[7.1]
  def change
    add_foreign_key :users, :GenderOptions, column: :gender_id, primary_key: :id

  end
end

But I get the same error.

In Rails, how would do I update an existing column in an existing table to make it a foreign key referencing another table?

Answer 1

In case anyone else has the same problem, the workaround solution I use was to make the changes in Workbench with SQL instead of through Rails migration:

1. Make sure all values for the attribute are set to NULL and make sure the data type for the reference attribute primary key and the attribute foreign key are the same (BIGINT)
2. Run alter table users add constraint gender_forkey foreign key (gender) references genderoptions (id)
3. In command line for Rails project run rake db:schema:dump