Foreign key violation on sql dump into ecto generated db

Questions / Help
,
1

I have an existing database which I wish to “mimic” in ecto. Here’s what I want to do:

generate all tables with gen.phx.schema commands – DONE
include has_many() relationships – DONE
remove timestamp() because it conflicts with a pre-existing updated_at column – DONE
generate db with mix ecto.create; mix ecto.migrate – DONE
dump the original database into the empty db with the command: psql kjvrvg_dev < db-data.pgsql > /dev/null– FAILS with

ERROR:  insert or update on table "mediamusic" violates foreign key constraint "mediamusic_music_id_fkey"
DETAIL:  Key (music_id)=(7) is not present in table "music".

I also get the error:

ERROR:  multiple primary keys for table "book" are not allowed
ERROR:  multiple primary keys for table "booktitle" are not allowed
and so on, for each table being dumped

here is how I generate music and mediamusic:

mix phx.gen.schema Music music absolute_id:integer uuid:uuid basename:string
mix phx.gen.schema MediaMusic mediamusic absolute_id:integer uuid:uuid track_number:integer localizedname:string path:string language_id:string presenter_name:string source_material:string music_id:references:music

I add this to music.ex:
has_many :mediamusic, Allscripture.MediaMusic

note the music_id:references:music, this should refer to the ecto auto-generated id primary key, shouldn’t it? I’m not sure why postgresql fails on this. Here are the tables defined in psql:

kjvrvg_dev=# \d+ music
                                                       Table "public.music"
   Column    |          Type          |                     Modifiers                      | Storage  | Stats target | Description 
-------------+------------------------+----------------------------------------------------+----------+--------------+-------------
 id          | bigint                 | not null default nextval('music_id_seq'::regclass) | plain    |              | 
 absolute_id | integer                |                                                    | plain    |              | 
 uuid        | uuid                   |                                                    | plain    |              | 
 basename    | character varying(255) |                                                    | extended |              | 
Indexes:
    "music_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "mediamusic" CONSTRAINT "fk_mediamusic_music_id_music" FOREIGN KEY (music_id) REFERENCES music(id)
    TABLE "mediamusic" CONSTRAINT "mediamusic_music_id_fkey" FOREIGN KEY (music_id) REFERENCES music(id)

and mediamusic:

kjvrvg_dev=# \d+ mediamusic
                                                         Table "public.mediamusic"
     Column      |          Type          |                        Modifiers                        | Storage  | Stats target | Description 
-----------------+------------------------+---------------------------------------------------------+----------+--------------+-------------
 id              | bigint                 | not null default nextval('mediamusic_id_seq'::regclass) | plain    |              | 
 absolute_id     | integer                |                                                         | plain    |              | 
 uuid            | uuid                   |                                                         | plain    |              | 
 track_number    | integer                |                                                         | plain    |              | 
 localizedname   | character varying(255) |                                                         | extended |              | 
 path            | character varying(255) |                                                         | extended |              | 
 language_id     | character varying(255) |                                                         | extended |              | 
 presenter_name  | character varying(255) |                                                         | extended |              | 
 source_material | character varying(255) |                                                         | extended |              | 
 music_id        | bigint                 |                                                         | plain    |              | 
Indexes:
    "mediamusic_pkey" PRIMARY KEY, btree (id)
    "mediamusic_music_id_index" btree (music_id)
Foreign-key constraints:
    "fk_mediamusic_music_id_music" FOREIGN KEY (music_id) REFERENCES music(id)
    "mediamusic_music_id_fkey" FOREIGN KEY (music_id) REFERENCES music(id)

Finally here are music.ex and media_music.ex:

music.ex:

defmodule Allscripture.Music do
  use Ecto.Schema
  import Ecto.Changeset
  alias Allscripture.Music


  schema "music" do
    field :absolute_id, :integer
    field :basename, :string
    field :uuid, Ecto.UUID

    # timestamps()
    has_many :mediamusic, Allscripture.MediaMusic
  end

  @doc false
  def changeset(%Music{} = music, attrs) do
    music
    |> cast(attrs, [:absolute_id, :uuid, :basename])
    |> validate_required([:absolute_id, :uuid, :basename])
  end
end

media_music.ex:

defmodule Allscripture.MediaMusic do
  use Ecto.Schema
  import Ecto.Changeset
  alias Allscripture.MediaMusic


  schema "mediamusic" do
    field :absolute_id, :integer
    field :language_id, :string
    field :localizedname, :string
    field :path, :string
    field :presenter_name, :string
    field :source_material, :string
    field :track_number, :integer
    field :uuid, Ecto.UUID
    field :music_id, :id

    # timestamps()
  end

  @doc false
  def changeset(%MediaMusic{} = media_music, attrs) do
    media_music
    |> cast(attrs, [:absolute_id, :uuid, :track_number, :localizedname, :path, :language_id, :presenter_name, :source_material])
    |> validate_required([:absolute_id, :uuid, :track_number, :localizedname, :path, :language_id, :presenter_name, :source_material])
  end
end
2

dump the original database into the empty db with the command: psql
kjvrvg_dev < db-data.pgsql > /dev/null-- FAILS with

ERROR: insert or update on table “mediamusic” violates foreign key
constraint “mediamusic_music_id_fkey”
DETAIL: Key (music_id)=(7) is not present in table “music”.

You need to turn off referential integrity checks before starting the
data load and re-enable them after.

Alternatively, use the generated db as a prototype and write the DDL
statements to convert a copy of your original db to exactly that form.

HTH!

3

Thanks for help Hassan. I’m not a dba, just a generalist so a lot of sql details like this are new to me. Can you give me some pointers on perhaps the DDL method? I was searching around and I saw the ‘–schema-only’ flag for pgdump which generates a DDL. Is that what you mean by writing a DDL? I have access to the original db. If this is a dead-end for me, how do I switch off/on the referential checks?

Regards,
Michael

4

Thanks for help Hassan. I’m not a dba, just a generalist so a lot of sql
details like this are new to me. Can you give me some pointers on perhaps
the DDL method? I was searching around and I saw the ‘–schema-only’ flag
for pgdump which generates a DDL. Is that what you mean by writing a DDL? I
have access to the original db. If this is a dead-end for me, how do I
switch off/on the referential checks?

Sorry, by “writing DDL” I meant looking at where your two schemas
differ and running commands to e.g. change a column name or data
type or whatever until the “old” db matches the generated one.

However, apparently Postgres (unlike MySQL) offers no easy way to
disable integrity checks, other than on a table-by-table basis.

GIYF → ‘postgres disable foreign key constraints’

While you might not have enough tables to make that dance onerous,
taking the other approach might be quicker. Either way you’ll be adding
to your SQL arsenal :grinning:

Have fun!

5

Yes, seems

ALTER TABLE the_table DISABLE TRIGGER ALL;

(Do import)

ALTER TABLE the_table ENABLE TRIGGER ALL;

Worked just fine… thanks