Ecto has_many :on_delete protect

I don’t think that django on_delete=Protect would protect the roles. It prevents referenced objects from being deleted.

Isn’t django PROTECT an equivalent of SQL RESTRICT?

It’s been a while since I used django, but if so, on_delete: :restrict should do what you want.

For you case, you should use on_delete: :restrict or on_delete: :nothing.

Why?

In short, read h Ecto.Migration.references.

In detail:

• :on_delete option specifies the behavior of referencing rows when a referenced row is deleted.
• When you call add :role_id, references(:role, on_delete: :delete_all), null: false, we say that business_user references role:
  • rows in business_user are the referencing rows.
  • rows in role are the referenced rows.
• :on_delete has 4 possible values:
  • :delete_all
    • PostgreSQL clause: ON DELETE CASCADE
    • description: The referencing rows will be deleted.
  • :nilify_all
    • PostgreSQL clause: ON DELETE SET NULL
    • description: The foreign key of referencing rows will be set as NULL.
  • :restrict
    • PostgreSQL clause: ON DELETE RESTRICT
    • description: Prevent deletion of a referenced row.
  • :nothing (default behavior) ::
    • PostgreSQL clause: NO ACTION
    • description: Do nothing. (It will cause an error when checking constrains.)

You can try to read the book - Programming Ecto, it will clear all you confusions and give you new insights.

As supplementary:

• above code just do data consistency, it is not helpful for user experience —— You shouldn’t just popup a model, and tell users “database error occurs”.
• try to use Ecto.Changeset.no_assoc_constraint before deleting any referenced rows, :on_delete is just the last line of defense for data consistency.

I am not very sure if django PROTECT is = SQL RESTRICT

didn’t know there is such :restrict, cannot find this in the ecto docs. anyway thanks.

read the detailed explanation above, i think :restrict is the one I am looking for

thanks for the datailed explanation! one question for the :nothing, does it delete any data? example, if i delete a role, the associated business_user data and the role data remained and raise error?

It seems like PROTECT is a bit more strict than the SQL RESTRICT, but it is also purely application code, on_delete on django models is not a db constraint.

And the :restrict is in the Ecto.Migration docs.

Unless you have a good reason or is handling it at the application level, it is nor recommended to use :nothing for references because it will make the data in the db inconsistent, pointing to role_ids that do not exist anymore, if you want to just remove all the references from the business_user you could use :nilify_all, it will not prevent the deletion of a role, but when the role is deleted all references pointing to it will be set to NULL.

yes it is not db constraint, django side only.

no wonder it is very hard for me to look for the full info, i was looking at Ecto v3.14.0 — Documentation and the docs has room for improvement.

Noted, I think i have read it somewhere but cannot find the doc. I will just go with :restrict then. Thanks for the good info!

It will trigger database constraint error, and won’t delete any data.

Thanks, again didn’t know about Ecto.Changeset.no_assoc_constraint. will do!

and

will do!