#ecto #ecto-migrations Reversible `references()` migration with from: option

how do I make a reversible reference migration, i.e. adding the referential constraint on “up” action and dropping it on “down” action, using from: keyword?
I’ve a pretty big migration and it’s much easier to use “def change” than “def up” and “def down”.

The documentation for references/3 says it can be used like such:

# Modify the :on_delete option of an existing foreign key
alter table("comments") do
  modify :post_id, references(:posts, on_delete: :delete_all),
    from: references(:posts, on_delete: :nothing)

which, I assume, would drop and recreate the constraint and is safe to use in “change” mode. How do I write one where “from” is a “no FK constraint” state?

1 Like

What are you trying that’s not working?

if I have

def change do
  alter table do
    modify column, references(:related, on_delete: :delete_all, validate: false)

ecto complains that ** (Ecto.MigrationError) cannot reverse migration command: alter table public.my_table. You will need to explicitly define up/0 and down/0 in your migration.

So I thought if I use a proper form of from: keyword like in the example in the documentation then it would become reversible. I’d prefer to use that form within change instead of having to break up the pretty lengthy migration into up and down migrations. So what’s the from: value to be used for “no foreign key constraint”?

Without the actual code that you are using it’s hard to give better help than pointing to the docs you’ve already quoted.

There’s also the option to use Ecto.Migration — Ecto SQL v3.11.1, which wouldn’t require you to break up your lengthy migration and provide yourself the means to define the reversal needed.

My code, in the “normal” case, would add a previously non-existent foreign key constraint with referential macro. The inverse (rollback) case would be for the constraint to be dropped, so I found that ecto documentation’s example where normal and reverse cases are both using referential to be insufficient to figure out what the from: clause should be for “no constraint”.

So it would still have to be some form of execute with SQL statements (e.g. create constraint if not exists... and drop constraint if exists) as opposed to being able to use the from: keyword’s argument to reverse the creation of foreign key constraint?

I applaud you for your curiosity. In my case when I stumble upon this I just write the up and down parts of the migration manually and move on.

I’d be inclined to think that’s the more likely outcome, yes.

1 Like