Martijn Tonies wrote:
> Hi,
>
>
>>[snip]
>>
>>>If you insert an invalid value into an ENUM (that is, a string not
>>>present in the list of allowed values), the empty string is inserted
>>>instead as a special error value. This string can be distinguished
>>
>>from
>>
>>>a 'normal' empty string by the fact that this string has the numerical
>>>value 0. More about this later.
>>
>>"changeday" is NOT NULL -> but you still can enter
>>'' ... Strange and, IMO, a bug. What's the use of
>>restricting to a set of possible values without enforcing it?
>>[/snip]
>>
>>On an INSERT it allows you to get or track entry errors. You have to
>>apply error checking to your application to enforce the integrity of the
>>enum field.
>
>
> So, basically, an ENUM has no real use when it comes to
> checking its values?
>
>
>>This has been one of those excessively debated issues over
>>the years.
>
>
> I sure hope so :-) ... I'm still new to MySQL though :-)
>
>
>>I have used this 'feature' for error checking for a long time
>
This usually comes up when people expect an exception to be thrown when
they assign NULL to a NOT NULL column, but this is the same idea. From
the manual <http://www.mysql.com/doc/en/constraint_NOT_NULL.html>:
> To be able to support easy handling of non-transactional tables all
> fields in MySQL have default values.
>
> If you insert a 'wrong' value in a column like a NULL in a NOT NULL
> column or a too big numerical value in a numerical column, MySQL will
> instead of giving an error instead set the column to the 'best
> possible value'.
In the case of ENUMs, "best possible" means the special error value
which is 0 in numeric context and '' in string context.
Supporting non-transactional tables is the key here. Throwing an error
in the middle of a multi row insert is a problem if you cannot roll
back. Hence, data integrity checking is the responsibility of the
client/programmer.
Michael
