Error with M2O and NOT Allowing NULL

[benhaynes]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

I get a foreign key constraint error when trying to create/edit a M2O field with Nullable disabled (unchecked).

To Reproduce

1. Create M2O field
2. Turn "All NULL Value" Off
3. Hit Save
4. See error

Errors Shown

See above.

What version of Directus are you using?

RC.99

What version of Node.js are you using?

7.20.3

What database are you using?

MySQL 5.7.26

What browser are you using?

Chrome

What operating system are you using?

MacOS

How are you deploying Directus?

locally

[Oreilles]

This is expected: the onDelete action is SET NULL- Which means when deleting the referenced item, the database would try to set this column to NUL,L which would break this constraint - so MySQL prevent you from doing this.

( Interestingly, PostgreSQL doesn't complain about it until we actually try to delete the referenced item ).

We could disable the onDelete option and set it to CASCADE automatically when a user makes a relational field non nullable like in your case, but it might not be expected to some users and lead to mistakes. Ultimately it's probably better to leave it as is...

[benhaynes]

Ah, that makes sense but it was not obvious to me at all. I'd be fine disabling one of the two, or throwing a notification when both are set... at least explaining to users that they can't do this (before they save and get this technical error).