Add reference to existing column

Questions / Help
,
1

Here is my original migration:

def change do
  create table(:clipboard_items) do
    add(:clipboard_id, :integer)

    timestamps()
  end
end

During this migration, I forgot to add the reference for each of these fields. It should have looked like this:

def change do
  create table(:clipboard_items) do
    add(:clipboard_id, references(:clipboards))

    timestamps()
  end
end

How can I add the reference to the existing table?

I’ve tried this:

  def change do
    alter table(:clipboard_items) do
      modify :clipboard_id, references(:clipboards)
    end
  end

But I get this error:

alter table clipboard_items
** (Postgrex.Error) ERROR 42710 (duplicate_object) constraint "clipboard_items_clipboard_id_fkey" for relation "clipboard_items" already exists

I’m not able to rollback because that migration has already run on production

This is making so I can’t cascade delete, any help on this would be great!

2

Turns out this will work:

defmodule Dreamhouse.Repo.Migrations.AddReferenceToClipboardItem do
  use Ecto.Migration

  def up do
    execute "ALTER TABLE clipboard_items DROP CONSTRAINT clipboard_items_clipboard_id_fkey"

    alter table(:clipboard_items) do
      modify :clipboard_id, references(:clipboards, on_delete: :delete_all)
    end
  end

  def down do
    execute "ALTER TABLE clipboard_items DROP CONSTRAINT clipboard_items_clipboard_id_fkey"

    alter table(:clipboard_items) do
      modify :clipboard_id, references(:clipboards, on_delete: :nothing)
    end
  end
end

VIA: Github Issue

2 Likes