Modify join table to reference different tables and rename

Question

I currently have a table called leagues_questions, but during development I've realized that the table should be called seasons_questions and reference the season table instead of the question table. I'm struggling to understand to best way to make this change. My first thought is to create a new migration that will modify this table. Here is my attempt:

Old Migration:

def change do
  create table(:leagues_questions, primary_key: false) do
    add :id, :uuid, primary_key: true
    add :league_id, references(:leagues, on_delete: :nothing, type: :uuid), null: false
    add :question_id, references(:questions, on_delete: :nothing, type: :uuid), null: false

    timestamps()
  end
end

NEW Migration:

def change do
  alter table(:questions_leagues) do
    modify :question_id, :season_id
  end
end

I also need to change the table name but I think I can handle that.

I don't think this will work and I haven't really even tried it yet, because I don't know how to change the references part. How can I modify the references column of a table within a migration?

Answer 1

To rename the column you need to do

rename table(:leagues_questions), :question_id, to: :season_id

Then you need to deal with the foreign key constraints

I'm assuming you've already tried this:

alter table(:leagues_questions) do
  modify :season_id, references(:seasons, on_delete: :nothing, type: :uuid), null: false)
end

and it didn't work. You need to do this:

drop constraint("leagues_questions", :leagues_questions_question_id_fkey)
alter table(:leagues_questions) do
  modify :season_id, references(:seasons, on_delete: :nothing, type: :uuid), null: false)
end

Basically drop the existing constraint first.