Consider changing On{Update,Delete} FK default behaviour from RESTRICT to NO ACTION

[hdgarrood]

At the moment, the OnUpdate and OnDelete behaviour for a foreign key declared in a persistentmodels file defaults to RESTRICT if not specified. However, I think NO ACTION might be a better default: it matches all supported SQL databases' defaults, and for databases that support it, it offers a little more flexibility without losing data integrity guarantees (because the FK is still checked, just not until the end of the transaction, as opposed to immediately).

• In MySQL, there is no difference between RESTRICT and NO ACTION: https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html#foreign-key-referential-actions, so changing the default will make no difference.
• In PostgreSQL, the difference is that RESTRICT checks immediately, whereas NO ACTION defers the check until the end of the database transaction. https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
• In sqlite, NO ACTION is the default, and (as far as I can tell) it works the same as PostgreSQL: https://www.sqlite.org/foreignkeys.html#fk_actions

I didn't see much previous discussion of this in the change that initially implemented the ability to specify OnUpdate/OnDelete in persistentmodels files (#1145) but please do let me know if I've missed anything.

This change would of course have to wait for a breaking release if we were going to do it, and it would generate a number of new migrations for existing databases that were previously considered to have their DB schemas up to date, but I think we usually consider this acceptable in breaking releases?

[parsonsmatt]

This seems like a reasonable change.

To make migration easier, probably best to have a new configuration option for "default foreign key action", which is used when nothing is specified. Then users can say "Continue using RESTRICT, I don't want to update this behavior."

[ldub]

Should there be a way to specify NO ACTION in persistent? Reading the docs I expected to see an OnDeleteNoAction and OnUpdateNoAction options.

[hdgarrood]

Yeah, I think we'd need a new NoAction constructor for the CascadeAction data type first.