Error saying column "id" referenced in foreign key constraint does not exist

Questions / Help
1

Hi all. I am trying to seed a database table with sales territories that have a many to many relationship with states (one territory has many states and one state has many territories) I have a function that allows me to upload a csv file with all the territory and state information that works fine. However I am trying to change the primary key on the territories table to territory_id instead of an autogenerated one in postgres.

Here are my territories and states schemas and then my migrations:

territory.ex

  @primary_key {:territory_id, :string, []}
  
  schema "territories" do
    field(:territory_name, :string)
    field(:ownerid, {:array, :string})
    

    #timestamps() getting db connection error this fixed.
    
    belongs_to(:sub_verticals, SubVertical, foreign_key: :sub_vertical_id, on_replace: :delete)  
    has_many(:users, User)
    many_to_many(:states, State,
      join_through: "territories_states",
      join_keys: [territory_id: :territory_id, state_id: :id],
      on_replace: :delete, on_delete: :delete_all) 
  end

state.ex

  schema "states" do
    field(:name, :string)

    timestamps()

    has_many(:cities, City)
    has_many(:new_cities, NewCity)
    belongs_to(:country, Country)
    has_many(:prospect, Prospect)
    many_to_many :territories, Territory,
      join_through: "territories_states", #, on_replace: :delete
      join_keys: [state_id: :id, territory_id: :territory_id]
  end

create_territories_table migration

defmodule Gimli.Repo.Migrations.CreateTerritoriesTable do
  use Ecto.Migration

  def change do
    create table(:territories, primary_key: false) do
      add :territory_id, :integer, primary_key: true
      add :territory_name, :string
      add :sub_vertical_id, references(:sub_verticals, on_delete: :delete_all)
      add :ownerid, :string
    

      timestamps()
    end
  end
end

create territories-states table migration

defmodule Gimli.Repo.Migrations.CreateTerritoriesStatesTable do
  use Ecto.Migration

  def change do
    create table(:territories_states) do
      add :state_id, references(:states)
      add :territory_id, references(:territories, on_delete: :delete_all)
    end
    create unique_index(:territories_states, [:state_id, :territory_id], on_delete: :delete_all)
  end
end

I’m pretty sure I have my join_keys setup correctly and haven’t been able to figure out what else would be causing this error:

14:21:06.393 [info]  create table territories_states
** (Postgrex.Error) ERROR 42703 (undefined_column) column "id" referenced in foreign key constraint does not exist
    (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.11.3) lib/enum.ex:1411: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:686: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.4.5) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.4.5) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.11.3) lib/enum.ex:2193: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.4.5) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (stdlib 3.14) timer.erl:166: :timer.tc/1
    (ecto_sql 3.4.5) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/7
    (ecto_sql 3.4.5) lib/ecto/migrator.ex:342: Ecto.Migrator.attempt/7
    (ecto_sql 3.4.5) lib/ecto/migrator.ex:243: anonymous fn/4 in Ecto.Migrator.do_up/4
    (ecto_sql 3.4.5) lib/ecto/migrator.ex:324: anonymous fn/3 in Ecto.Migrator.run_maybe_in_transaction/6
    (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:875: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection 2.2.2) lib/db_connection.ex:1427: DBConnection.run_transaction/4
    (ecto_sql 3.4.5) lib/ecto/migrator.ex:323: Ecto.Migrator.run_maybe_in_transaction/6
    (elixir 1.11.3) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (elixir 1.11.3) lib/task/supervised.ex:35: Task.Supervised.reply/5
    (stdlib 3.14) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
(base) justinbenfit@MacBook-Pro-3 gimli % iex -S mix
Erlang/OTP 23 [erts-11.1.4] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [hipe]

Any help/direction appreciated.
Thanks!

2

This is causing the error; the settings for join_key etc in the schema don’t affect migrations. You’ll need to pass column and type.

2 Likes
3

Thank you @al2o3cr! That appears to have fixed that problem: I don’t know for sure yet because now I am getting this error:

** (Ecto.ChangeError) value `["0051a0000034hCD", "0055G000007G9yX", "0055G000007HGcb", 
"0051P000003mZaJ", "0051a00000364Pb", "0055G000007H4SP", "0051P000003mm4f", 
"0051P000003mjz0", "0055G000007H5s4", "0055G000007HOBP", "0055G000007GPZs", 
"0051P000003mjz0", "0055G000007H5s4", "0055G000007HOBP", "0055G000007GPZs", 
"0051P000003mjz0", "0055G000007H5s4", "0055G000007HOBP", "0055G000007GPZs"]` for 
`Gimli.Prospector.Assignment.Territory.ownerid` in `insert` does not match type :string

in a previous database table with the same setup I was able to manually (not changing any elixir schemas but rather changing in postico directly) change the type of ownerid to character varying[] and input lists of string such as the value pasted above. For some reason I am no longer able to do so and the ecto docs say to just use type :string in the schema but that’s not working. Any ideas? Thanks!

Here is the relevant schema currently:

  schema "territories" do
    field(:territory_name, :string)
    field(:ownerid, :string)
    

    timestamps() 
    
    belongs_to(:sub_verticals, SubVertical, foreign_key: :sub_vertical_id, on_replace: :delete)  
    has_many(:users, User)
    many_to_many(:states, State,
      join_through: "territories_states",
      join_keys: [territory_id: :territory_id, state_id: :id],
      on_replace: :delete, on_delete: :delete_all) 
  end
4

This schema declaration will not work with this migration:

You’d want add :ownerid, {:array, :string} to prevent the error you’re seeing.

3 Likes
5

That worked! Thanks!