Datatype_mismatch on migration where type is not changed

Phoenix Forum Questions / Help
, ,
1

I’m trying to add an “on_delete: :delete_all” value to a foreign key constraint on a table and am seeing an error that I’ve not seen doing this. Heres’ the migraton:

def up do
    drop constraint("answers", "answers_response_id_fkey")
    alter table(:answers) do
      modify(:response_id, references(:form_responses, on_delete: :delete_all))
    end
  end

  def down do
    drop constraint("answers", "answers_response_id_fkey")
    alter table(:answers) do
      modify(:response_id, references(:form_responses, on_delete: :nothing))
    end
  end

I’ve run this type of adjustment on other tables with no problem. Here, Ecto is kicking back this error:

alter table answers
** (Postgrex.Error) ERROR 42804 (datatype_mismatch) column “response_id” cannot be cast automatically to type bigint

The mystery is that the columns involved are all strings, there is nothing typed to bigint in these tables. I’m wondering if the fact that this column is part of a two-column primary key may be the rogue variable. And if so, what might the proper syntax be to kill the primary, do the fkey change and revive the primary in this up/down format?

2

Maybe use type parameter… like this.

modify(:response_id, references(:form_responses, type: :string, on_delete: :nothing))
2 Likes
3

What type is response_id?

4

strings…

5

Oh. Right. Had no idea a reference can be made in Ecto through a string. :smiley:

6

I figured this out. Apparently this is something that happens when using non-bigint foreign keys. The solution is to not only call out the datatype as @kokolegorille noted but to also explicitly reassert the column name. So where this throws a datatype_mismatch error,

def up do
    drop constraint("answers", "answers_response_id_fkey")
    alter table(:answers) do
      modify(:response_id, references(:form_responses, on_delete: :delete_all))
    end
  end

  def down do
    drop constraint("answers", "answers_response_id_fkey")
    alter table(:answers) do
      modify(:response_id, references(:form_responses, on_delete: :nothing))
    end
  end

This version works (the column in the form_responses table just happens to coincidentally have the same same as the column in the answers table, thus they are both response_id).

  def up do
    drop constraint("answers", "answers_response_id_fkey")
    alter table(:answers) do
      modify(:response_id, references(:form_responses, column: :response_id, type: :string, on_delete: :delete_all))
    end
  end

  def down do
    drop constraint("answers", "answers_response_id_fkey")
    alter table(:answers) do
      modify(:response_id, references(:form_responses, column: :response_id, type: :string, on_delete: :nothing))
    end
  end

Thanks for pointing me in the right direction!

3 Likes
7

Here’s a better explanation of why this happened and why the workaround was needed, based on some research I did. Please add any corrections if this is incorrect.

I turns out that as of Ecto 2.0, primary keys default to bigint and if your foreign key is a primary in the target table and not an int, you should ideally define that in the schema with something like @foreign_key_type :string .

Without that, migrations will stumble and try to recast the datatype. If that definition is not in the schema, the workaround is to explicitly call out both the column name and datatype in the migration and then it will execute smoothly.

2 Likes