Ecto reversed foreign key

Hello elixir folks, I am new in elixir programming and I am trying to migrate two databases with a foreign key in both tables but it doesn’t seem to work ??

  def change do
    create table(:users) do
    add :username, :string
    add :email, :string
    add :name, :string
    add :password, :string
    add :address, :string


    timestamps()
    end

    alter table("users") do
          add :organization, references(:organization)
    end

  end
  def change do
    create table(:organization) do
      add :org_key, :string
      add :name, :string

      timestamps()
    end
    create unique_index(:organization, [:org_key])

    alter table("organization") do
            add :creator, references(:users)

    end
  end

By default Ecto is going to use the singular name of the table with _id appended for a FK.

If you want to use different column names I would check out the documentation for references/2 Ecto.Migration — Ecto SQL v3.6.1

The migrations can’t be done, because every table depends on the another, i am trying to implement bidirectionally foreign key and idea??

Ah, you may have to disable the transactions. Try adding this @disable_ddl_transaction true at the top of your migration module

Not working , I am getting “(Postgrex.Error) ERROR 42P01 (undefined_table) relation “users” does not exist” error

One or the other of these migrations will need to be split into two parts - the table being referenced needs to exist before the foreign key can be added.

Something like:

  • create users, don’t add the foreign key
  • create organizations with a foreign key to users
  • add a foreign key from users to organizations
2 Likes

I tried it, the last step when I add a foreign key from users to organizations the migration doesn’t apply

If it’s failing with the undefined_table error mentioned in your previous message, that means the first step (creating the users table) did not work.

I suspect you’ve got migrations which don’t match the actual database - it’s easy to get them out-of-sync when editing and re-running - you may want to recreate your DB, or clean up schema_migrations manually.

it’s not failing, the user table migration works, after the organization migrate too but without applying the changes on the users’ table (alter table not working )

You’ll need to do it in 3 steps.

  1. Create the users table (without organization FK referencing organization since that table doesn’t exist at this point in time)
  2. Create the organization table (with the creator foreign key referencing users)
  3. Add organization foreign key to users referencing organization
# 1 _create_users_table.exs migration
  def change do
    create table(:users) do
      add :username, :string
      add :email, :string
      add :name, :string
      add :password, :string
      add :address, :string

      timestamps()
    end
  end
# 2 _create_organization_table.exs migration
  def change do
    create table(:organization) do
      add :org_key, :string
      add :name, :string
      add :creator, references(:users)

      timestamps()
    end
    
    create unique_index(:organization, [:org_key])
  end
# 3 _add_organization_to_users.exs migration
  def change do
    alter table("users") do
      add :organization, references(:organization)
    end
  end

I should remember to read all the previous replies next time :laughing:

2 Likes