Ecto has_many :on_delete protect

Phoenix Forum Questions / Help
,
1

Hi awesome people,

I have a User and Role table. Each role has many users.

Here is the table structure in ecto migration file.

    create table(:business_user, primary_key: false) do
      add :business_id, references(:businesses, on_delete: :delete_all), null: false
      add :user_id, references(:users, on_delete: :delete_all), null: false
      add :role_id, references(:role, on_delete: :delete_all), null: false

      timestamps()
    end

I know on_delete: :delete_all) will delete all related business_users if the role is deleted. But how to i protect this role from accidentally deleted?

Because i came from Django, it has on_delete=PROTECT to protect the role from deleted and error raised. I am new to ECTO, i not sure if there is anything I am missing here :slight_smile:

2

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

3

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.

1 Like
4

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.

1 Like
5

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.
2 Likes
6

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

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 :slight_smile:

7

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?

8

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.

9

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.11.1 — 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!

10

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

1 Like
11

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

and

will do!

12

Ecto is way more powerful than a simple database wrapper and can fetch and query data from basically any source, you can set up an Ecto.Repo that can fetch/update answers from a json api (or multiple data sources at once) and format/validate it using an Ecto.Schema and your app wouldn’t have any idea that it would be data from an json api and not an DB, because of this the Ecto documentation focus in the most generic abstracted parts, migrations are something pretty much SQL related and they are in their own ecto_sql module, but I agree that for someone that isn’t used to it it can be a bit confusing.

13

ah i see! will look into that! thanks

yes confused and loved. :slight_smile:

14

Hey, don’t forget that part of the Repo config options that you can supply in your config/**.exs files (only those that pertain to actual DB connections, and they are a lot) are actually documented inside DBConnection.start_link/2. :003:

2 Likes