How to fix ERROR 42830 (invalid_foreign_key) in Migrations

Questions / Help
,
1

Hello, when I want to migrate my migrations I have this error:

ERROR 42830 (invalid_foreign_key) there is no unique constraint matching given keys for referenced table "invoice_statuses"

invoice_statuses migration

defmodule Household.Repo.Migrations.InvoiceStatuses do
  use Ecto.Migration
  @disable_ddl_transaction true

  def change do
    create table(:invoice_statuses, primary_key: false) do
      add :id, :uuid, primary_key: false
      add :name, :string, null: false
      timestamps()
    end
    create(
      index(:invoice_statuses, [:name],
        concurrently: true,
        name: :unique_index_on_invoice_statuses_name,
        unique: true
      )
    )
  end
end

invoices migration and relation

defmodule Household.Repo.Migrations.Invoices do
  use Ecto.Migration
  @disable_ddl_transaction true

  def change do
    create table(:invoices, primary_key: false) do
      add :id, :uuid, primary_key: false
      add :name, :string, null: false
      add :last_name, :string, null: false
      add :delivery_time, :utc_datetime, null: true
      add :price, :string, null: false
      add :mobile, :string, null: false
      add :description, :text, null: false
      add :code, :string, null: false

      add :invoice_status_id, references(:invoice_statuses, on_delete: :nothing, type: :uuid)
      add :brand_id, references(:brands, on_delete: :nothing, type: :uuid)
      add :device_id, references(:devices, on_delete: :nothing, type: :uuid)
      timestamps()
    end
    create(
      index(:invoices, [:code],
        concurrently: true,
        name: :unique_index_on_invoices_code,
        unique: true
      )
    )
  end
end

where am I wrong? I never have had or seen this error before!!!

2

You have disabled :id beeing the primary key, so you can not use it as a foreign key. Any column you want to use as a foreign key, needs to have an index defined at least.

1 Like
3

Thank you ,
I have always used this way please see my code in another project and I have had no error:

defmodule BankError.Repo.Migrations.BlogPosts do
  use Ecto.Migration
  @disable_ddl_transaction true

  def change do
    create table(:blog_posts, primary_key: false) do
      add :id, :uuid, primary_key: true

      add :title, :string, size: 150, null: false
      add :short_description, :text, null: false
      add :description, :text, null: false
      add :seo_alias_link, :string, size: 200, null: false
      add :seo_words, :string, size: 164, null: false
      add :seo_description, :string, size: 164, null: false
      add :image, :string, size: 200, null: false
      add :tags, :string, size: 164, null: false

      add :status, :boolean, null: false
      add :category_id, references(:blog_categories, on_delete: :nothing, type: :uuid)
      timestamps()
    end
    create(
      index(:blog_posts, [:seo_alias_link],
        # concurrently: true,
        name: :unique_index_on_blog_posts_alias_link,
        unique: true
      )
    )
  end
end

and

defmodule BankError.Repo.Migrations.BlogCategories do
  use Ecto.Migration
  @disable_ddl_transaction true

  def change do
    create table(:blog_categories, primary_key: false) do
      add :id, :uuid, primary_key: true

      add :title, :string, size: 150, null: false
      add :short_description, :string, size: 164, null: false
      add :description, :text,  null: false
      add :image, :string, size: 200, null: false
      add :seo_alias_link, :string, size: 200, null: false
      add :seo_words, :string, size: 150, null: false
      add :seo_description, :string, size: 164, null: false
      add :status, :boolean, null: false

      timestamps()
    end

    create(
      index(:blog_categories, [:seo_alias_link],
        # concurrently: true,
        name: :unique_index_on_blog_categories_alias_link,
        unique: true
      )
    )
  end
end

how can I defined it I mean the :id? should I create a new index like

create(
      index(:invoices, [:code],
        concurrently: true,
        name: :unique_index_on_invoices_code,
        unique: true
      )
    )

and my references should be changed ?

4

You have primary_key: false in the original post, thats probably the problem, as I said before.

2 Likes
5

I changed it to true add :id, :uuid, primary_key: true, and my migrations were completed, but I dont know why it worked me without primary_key: true before!?

6

Where do you do not have primary_key: true?

Except for the code that did not work, you have it everywhere…

1 Like
8

you are right, I made bad mistake, I don’t know where I copied this false code :thinking:
Thanks