Cannot create an M2O field as NOT NULL

[HitomiTenshi]

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

After creating a new M2O field the "Allow NULL value" gets checked and cannot be unchecked.

Might be related to #11604 and #11510

To Reproduce

• Create a new M2O field, make sure "Allow NULL value" is unchecked.
• Inspect newly created M2O field, notice the checkbox is checked even though you have unchecked it before.
• Uncheck it and save again. Results in an error.

Errors Shown

[INTERNAL_SERVER_ERROR] alter table mytable modify test int not null - ER_FK_COLUMN_CANNOT_CHANGE: Cannot change column 'test': used in a foreign key constraint 'mytable_test_foreign'

What version of Directus are you using?

9.5.1

What version of Node.js are you using?

16.14.0

What database are you using?

MariaDB 10.3.32

What browser are you using?

Chrome latest

What operating system are you using?

Windows 10

How are you deploying Directus?

Ubuntu Server 20.04, npm installation with a systemd service running it

[azrikahar]

This should be the same underlying issue as #9089. Based on the reproduction steps, the default relational trigger created is Nullify,

hence why when you try to prevent NULL values now, the error pops up since there's a constraint that needs to SET NULL.

More info on this in the comment here: #9089 (comment)