MySQL enums empty value

Question

I am trying to understand how enums could be used in mysql. If I insert anything to enum field that is out of the enum type -- mysql inserts empty string (with value 0).

• fruit ENUM ('APPLE','BANANA','PEACH');
• INSERT ... fruit='BANNANA'

Simple misspelling and MySQL inserts empty value, breaks database integrity and makes enums extremely useless.

CHECK constraints could help here but MySQL does not support them (pretty funny for "most popular" database in 2011)

The only way I see is to write the trigger to prevent empty string but it is too much work to write trigger for such a simple case.

Does there is a way to disable "empty string" MySQL behavior for enums?

Thanks

Answer 1

Take a look at sql modes

SET SQL_MODE='TRADITIONAL';