Forbid deletion of a parent with children

Phoenix Forum Questions / Help
1

so i get this error when trying to delete a class that has associated students →

constraint error when attempting to delete struct:
foreign_key: students_class_id_fkey
If you would like to convert this constraint into an error, please
call foreign_key_constraint/3 in your changeset and define the proper
constraint name. The changeset has not defined any constraint.

i’m trying to use |> no_assoc_constraint(:students, message: "You can't delete a class with associated students.") but the error persists, my controller is like this → 

def delete(conn, %{"id" => id}) do
      class = Repo.get!(Class, id) |> Repo.preload([:students])
      changeset = Class.changeset(class)
      case Repo.delete(class) do
        {:ok, _} ->
          conn
          |> put_flash(:info, "Class deleted successfully.")
          |> redirect(to: class_path(conn, :index))
        {:error, changeset} ->
          render(conn, "index.html", class: class, changeset: changeset)
        end
    end

what do i need to change ? thanks!

2

Hmm. From what I see, it’s not your code that have a problem, but it’s your table definition problem. So you have a classes table and a students table, and your students table have a foreign key constraint to the classes table, and now, you want to delete a row in the classes table, correct? If you’re not defining the ON DELETE constraint on your table definition, this will lead to the DBMS use the default value which I believe is RESTRICT. This is the default mechanism so there’re no orphans data in a database. If you want to make this work, you might want to add the appropriate constraint in your Class schema relation

has_many :students, MyApp.Student, on_delete: :delete_all

with this, when you delete the parent row, it will also delete all the related children rows

3

Well, what you propose here is the opposite of what the OP wants!

You are proposing to delete all students that are in the class when the class gets deleted. The OP wants that you can’t delete a class when it still has associated students.

@RodrigoPinheiro, please do show the full changeset, and not only that singe constraint.

4

I’m really sorry, I didn’t read the full context and jump straight to a conclusion. My bad :icon_sad:
@RodrigoPinheiro, please show your changeset definition like @Nobbz suggest.

5

I’m trying to do the same thing and I run into the same error.

This is my changeset definition:

def changeset(struct, params \\ %{}) do
  struct
  |> cast(params, [:name, :amount])
  |> validate_required([:name, :amount])
  |> no_assoc_constraint(:transactions)
end
6

here is my changeset ->

   def changeset(struct, params \\ %{}) do
     struct
     |> cast(params, [:class, :period, :vacancies])
     |> cast_assoc(:students)
     |> validate_required([:class, :period, :vacancies]
     |> no_assoc_constraint(:students, message: "You can't delete a class with associated students.")
  end
7

no problem man, thanks for trying to help!

8

After some investigation, it looks like it is a bug in Ecto.

The constraint error that Postgres returns is something like foreign_key_name" on table "table_name, but Ecto looks for foreign_key_name as an exact match. It cannot find it so it fails and we get an error.

As a workaround, you can do:

def changeset(...) do
  ...
  |> no_assoc_constraint(:field, name: "foreign_key_name\" on table \"table_name")
  ...
end
9

Can you please file a bug report containing information on how to reproduce it? We have tests for the no_assoc_constraint feature and it does work under the scenarios tested so we are missing certain constraints.

10

@Thijs still doesn’t work here, can you show me your controller ?

11

I see it now.

You do Repo.delete(class), you should do Repo.delete(changeset).

3 Likes
12

Hahaha, I din’t see that coming