Foreign key error

Hi…

I am getting the following error when trying to use references() in a migration:

2018-01-30 17:02:33 700006a5c000 Error in foreign key constraint of table migrations_dev/clients:
 FOREIGN KEY (`client_group_id`) REFERENCES `client_groups`(`id`), `inserted_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE = INNODB:
Cannot resolve table name close to:
(`id`), `inserted_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE = INNODB

Here are my two migrations:

defmodule Migrations.Repo.Migrations.Clients do
  use Ecto.Migration

  def change do
    create table(:clients) do
      add :name, :string
      add :client_group_id, references ("client_groups")
      timestamps()
    end
  end
end

defmodule Migrations.Repo.Migrations.ClientGroups do
  use Ecto.Migration

  def change do
    create table(:client_groups) do
      add :name, :string
      timestamps()
    end
  end
end

I have tried to cut back my migrations to the most basic as possible and there is no issues running ecto.migration without the references() call… As far as I can tell my example matches the documentation for references().

Any ideas?

My researching suggests that it might be because I am not using INNODB, but from what I can tell - I am. Here is result of how tables are created if I run migrations without references()…

BTW, I am running MYSQL 5.6.28 under MAMP PRO with default config.

After doing some more reading, it appears as though the error message from MYSQL might be misleading…

Ok, silly me…

I was running my migrations in the wrong order… I haven’t used foreign key constraints before. So I created my client_groups table first and then created the clients table which depends on the client_groups table.

So in the future if I need to add another foreign key constraint to the clients table, I will need to write a migration for the parent table first…

Weird that there doesn’t seem to be a mention of this in the Ecto docs - I assume its just common DB admin knowledge that table creation order matters in these cases?

3 Likes