Distant relations, deleting, and getting database constraint errors into a changeset

Phoenix Forum Questions / Help
, ,
1

I have a Postgres database with these tables and associations:
A has many B, B has zero or one C, C has many D.

I have a form that manages a single A record and its child B records. When the user hits save, I want to prevent deleting a B record if it has any instances of D through table C. This is working properly with constraints at the database level:

delete from B where id = 123;
ERROR:  update or delete on table "C" violates foreign key constraint "D_C_id_fkey" on table "D"
DETAIL:  Key (id)=(456) is still referenced from table "D".

However, I can’t get the database error to propagate back to the A/B changesets and I instead get an exception. Is it possible with no_assoc_constraint (or anything else) to get that? Which module should the changeset constraint be applied to if the form only concerns itself with A/B and doesn’t preload or otherwise know about C/D?

I’ve tried putting has_many :d, through: [:c, :d] plus changeset |> no_assoc_constraint(:d) (with and without the constraint name option) in the B schema and that results in " cannot add constraint to changeset because association D does not exist."

2

This is a guess (that is to say I haven’t tested it), but I believe if you use foreign_key_constraint/3 with the name option set to the constraint which is actually violated it would apply the error to the field you choose.

You could do the same with no_assoc_constraint/3, it just uses an assoc name instead of the actual foreign key field.