While NULL is a concept familiar to SQL users FileMaker developers tend to have little exposure to the idea. Effectively NULL is the value given to a field who has no known value. It is distinct from an empty field or a field that contains the text NULL. NULL has some interesting characteristics most based around the fact that NULL ≠ NULL so SELECT * where field = NULL will never return any values; NULL never equals anything even itself. To test for NULL within MySQL the IS NULL function is used.
So what has this got to do with FileMaker?
A field defined as NOT NULL within MySQL has NOT EMPTY as a validation in FileMaker. Notice the subtle difference there? Because FileMaker has no effective NULL its the best FileMaker can do without some hacking in the MySQL. The best solution is to have MySQL to auto enter NULL but have the field set to NOT NULL. Within FileMaker have allow blank values. This does move the validation from FileMaker to MySQL and you need to insert a blank value into MySQL but it has the advantage of closer following the rules with NULL.
Unfortunately pushing a blank from FileMaker through ESS changes blank to NULL so you have to use a space or some dirty hack with a trigger at the MySQL end. A better alternative is to use the EXECUTE SQL script function from FileMaker in MySQL when amending, deleting and updating. Using the EXECUTE SQL script step has a number of advantages but principally here allows the use of stored procedures. Typically data amending functions within SQL databases are handled through stored procedures rather than directly amending the data. Stored procedures are preferred because they provide a single point of maintenance for a given function.
The advantages of using stored procedures from the FileMaker end may seem to outweigh the costs; the easy ESS is swopped for SQL. The advantages come with where the stored procedures are maintained. Assuming the MySQL database is managed by a DBA familiar with MySQL they will already prefer using stored procedures over trusting some alien system with carte blanche rights over their precious data. The key to creating advantage in FileMaker is planning the way FileMaker uses the stored procedure. The natural approach is to either create a function within a script, a custom function or a combination of the two. Effectively we have a single point of contact between the MySQL and the FileMaker.