List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:January 5 2004 4:18pm
Subject:Re: Enum default values
View as plain text  
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

Thread
Enum default valuesRichard Dyce5 Jan
RE: Enum default valuesJay Blanchard5 Jan
  • Re: Enum default valuesRichard Dyce5 Jan
    • Re[2]: Enum default valuesAleksandar Bradaric5 Jan
      • Re: Re[2]: Enum default valuesRichard Dyce5 Jan
  • Re: Enum default valuesMartijn Tonies5 Jan
RE: Enum default valuesJay Blanchard5 Jan
  • Re: Enum default valuesMartijn Tonies5 Jan
RE: Enum default valuesJay Blanchard5 Jan
  • Re: Enum default valuesMartijn Tonies5 Jan
    • Re: Enum default valuesMichael Stassen5 Jan
  • Re: Enum default valuesMartijn Tonies5 Jan