What do the on_delete options in Ecto.Migrations.r

Question

The Ecto documentation describes the options available to references/2, but does not document what those options do. The options available are:

• :nothing
• :delete_all
• :nilify_all
• :restrict

What do they do?

Answer 1

This is actually a SQL question at root.

https://github.com/elixir-ecto/ecto_sql/blob/52f9d27a7ad86442f442bad2f7ebd19ba09ddc61/lib/ecto/adapters/myxql/connection.ex#L902-L905

The PostgreSQL documentation outlines these options clearly:

• :nothing - if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything.
• :delete_all - specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well
• :nilify_all - causes the referencing column(s) in the referencing row(s) to be set to nil when the referenced row is deleted
• :restrict - prevents deletion of a referenced row. It will fail if there is a referenced object.

:nothing and :restrict are similar but:

the essential difference between these two choices is that [:nothing] allows the check to be deferred until later in the transaction, whereas [:restrict] does not.