Handling deletions on associated table with 2 indexes

This is a simplified toy example to illustrate my question.

The Orders table has 2 columns, :gateway_a_id and :gateway_b_id, respectively.
The Gateways table has 1 column, :gateway_type

Before creating an Order, 2 gateway records need to be created for each gateway_type, A and B, respectively.

On the UI, the Orders form contains 2 select menus, Gateway A and Gateway B, respectively.

When trying to delete a gateway record that is associated with an order, how can I resolve the following error:

Ecto.ConstraintError at DELETE /gateways/2 
constraint error when attempting to delete struct:
* foreign_key: orders_gateway_type_a_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. 

note for below: (on_delete: :nothing is intentionally used because the user must manually update the order to remove gateway type references before deleting a gateway)

def change do
    create table(:orders) do
      add :name, :string
      add :gateway_type_a_id, references(:gateways, on_delete: :nothing)
      add :gateway_type_b_id, references(:gateways, on_delete: :nothing)

    create index(:orders, [:gateway_type_a_id])
    create index(:orders, [:gateway_type_b_id])

def change do
 create table(:gateways) do
  add :name, :string
  add :gateway_type, :string

  # do I create 2 indices on gateways? 

schema "orders" do
 field :name, :string
 field :gateway_type_a_id, :id
 field :gateway_type_b_id, :id  

schema "gateways" do
field :gateway_type, :string

Note to moderator: Since this is Ecto related, I’m not sure if it belongs in Elixir or Phoenix.

1 Like

The solution was simpler than expected. It’s generalized below.

Set no_assoc_constraint

|> Ecto.Changeset.change
|> Ecto.Changeset.no_assoc_constraint(:table_name)
|> Repo.delete()

Update :delete action of the controller to use a case statement.

case MyModule.delete_resource(resource) do
 {:ok, _resource} ->
  |> put_flash(:info, "your message here")
  |> redirect(to: resource_path(conn, :index))
 {:error, %Ecto.Changeset{} = changeset} ->
  |> put_flash(:error, "Error #{inspect Changeset.errors}.")
  |> redirect(to: resource_path(conn, :index))
  1. The table (table A) with the foreign key needs a belongs_to in the schema.
  2. The other table (table B) needs a has_one or has_many in the schema. Your implementation may change if your association is many_to_many
  3. table A’s changeset/2 needs to cast the foreign key of the belongs_to reference. example:
    if belongs_to(:resource, My.Module.Resource) then the cast in the Changeset should be cast(attrs, [:resource_id] this assumes you are using the default naming convention.
  4. table A’s changeset/2 also needs a cast_assoc/2 piped to it, example: |> cast_assoc(:resource) (this does not need _id appended to it, if using default naming conventions).
  5. the migration of table A must include a references option and an index. for example:
create table(:things) do #this is table A
 add :resource_id, references(:resources, on_delete: :nothing)
create index(:things, [:resource_id])

I haven’t looked into customizing the error messages that are returned from ecto Changeset, but one clue is to see Ecto.Changeset.traverse_errors/2

Example code/use cases of Ecto.Changeset.traverse_errors/2 would be appreciated.:grinning:

1 Like