Fixing foreign_key_violation when deleting a table

Questions / Help
, ,
1

I’m trying to delete a User in my system. Any given user has_many Project and Projects have_many Rows.

I’m running into an issue when trying to delete a User here is the error message:

** (Postgrex.Error) ERROR 23503 (foreign_key_violation) update or delete on table "projects" violates foreign key constraint "rows_project_id_fkey" on table "rows"

    table: rows
    constraint: rows_project_id_fkey

Key (id)=(1) is still referenced from table "rows".
    (ecto_sql) lib/ecto/adapters/sql.ex:624: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql) lib/ecto/adapters/sql.ex:557: Ecto.Adapters.SQL.execute/5
    (ecto) lib/ecto/repo/schema.ex:436: anonymous fn/5 in Ecto.Repo.Schema.do_delete/3
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/repo/schema.ex:435: anonymous fn/10 in Ecto.Repo.Schema.do_delete/3
    (ecto) lib/ecto/repo/schema.ex:917: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto_sql) lib/ecto/adapters/sql.ex:820: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection) lib/db_connection.ex:1415: DBConnection.run_transaction/4

I took this to mean I had to set the on_delete: :delete_all property in my project.ex file but that doesn’t seem to help.

MODELS:

USER.EX:

has_many :projects, Dreamhouse.Project, foreign_key: :owner_id, on_delete: :delete_all

PROJECT.EX:

has_many :rows, Dreamhouse.Row, on_delete: :delete_all

ROW.EX:

has_many :images, Image, on_delete: :delete_all
has_many :comments, Comment, on_delete: :delete_all

I seem to have all that in place yet I’m still getting the error message above. Does anybody know why this is?

1 Like
2

If I understand correctly those samples of code are from your schemas?
I think that you need instead to define the action to perform on parent deletion, rather at migrations level.

Edit: See add/3 and its options.

Edit 2: So I came on this where searching in Ecto.Schema.has_many options.

:on_delete - The action taken on associations when parent record is deleted. May be :nothing (default), :nilify_all and :delete_all . Using this option is DISCOURAGED for most relational databases. Instead, in your migration, set references(:parent_id, on_delete: :delete_all) . Opposite to the migration option, this option cannot guarantee integrity and it is only triggered for Ecto.Repo.delete/2 (and not on Ecto.Repo.delete_all/2 ) and it never cascades. If posts has many comments, which has many tags, and you delete a post, only comments will be deleted. If your database does not support references, cascading can be manually implemented by using Ecto.Multi or Ecto.Changeset.prepare_changes/2 .

3 Likes