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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
