Elixir Ecto: How do I create a self-referential foreign key?

4
votes

What is the correct way to implement foreign keys which refer to their own table in an Ecto migration?

e.g. I would like to create a table where any row may reference a "parent" row in the same table. (One way to do hierarchical data; there are many other ways.)

But when I have this in my changeset, I get lots of errors when running mix ecto.migrate:

create_if_not_exists table(:perms, prefix: :accts) do
  add :title, :string, size: 64, null: false
  add :description, :text
  add :parent_id, :integer, references(:perms)
end

The error message begins with (UndefinedFunctionError) undefined function Ecto.Migration.Reference.fetch/2 (Ecto.Migration.Reference does not implement the Access behaviour) before GenServer terminates. (This occurs under ecto 1.1.5 and 2.0.0-beta2.)

1
elixirecto

1 Answers

6
votes

And the answer was a simple mistake: trying to specify the column type for a foreign key column causes an error. The correct syntax is (notice the missing :integer atom):

create_if_not_exists table(:perms, prefix: :accts) do
  add :title, :string, size: 64, null: false
  add :description, :text
  add :parent_id, references(:perms)
end