Enum data type

So I setup a field as an enum data type:

field1 enum('a','b','c')

So when I tried to insert a value 'd' I was surprised when I did not receive
an error - and instead the value was inserted as an empty string.

How can I enforce that only the enum values are entered - i.e. receive an
error when attempting to enter a non enum type value?

> So I setup a field as an enum data type:
>
> field1 enum('a','b','c')
>
> So when I tried to insert a value 'd' I was surprised when I did not
receive
> an error - and instead the value was inserted as an empty string.
>
> How can I enforce that only the enum values are entered - i.e. receive an
> error when attempting to enter a non enum type value?

ENUM doesn't enforce a check constraint.

It might be depending on some obscure setting though :-)

Alternatively, use triggers to force the value.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On 6 Mar, 13:26, "booner" <bbo...@insightbb.com> wrote:
> So I setup a field as an enum data type:
>
> field1 enum('a','b','c')
>
> So when I tried to insert a value 'd' I was surprised when I did not receive
> an error - and instead the value was inserted as an empty string.
>
> How can I enforce that only the enum values are entered - i.e. receive an
> error when attempting to enter a non enum type value?

Maybe you had the field set to allow null values

On 6 Mar, 13:26, "booner" <bbo...@insightbb.com> wrote:
> So I setup a field as an enum data type:
>
> field1 enum('a','b','c')
>
> So when I tried to insert a value 'd' I was surprised when I did not receive
> an error - and instead the value was inserted as an empty string.
>
> How can I enforce that only the enum values are entered - i.e. receive an
> error when attempting to enter a non enum type value?

Mid you, when I just tried it I got

#1265 - Data truncated for column 'field1' at row 1

On Mar 6, 1:26 pm, "booner" <bbo...@insightbb.com> wrote:
> So I setup a field as an enum data type:
>
> field1 enum('a','b','c')
>
> So when I tried to insert a value 'd' I was surprised when I did not receive
> an error - and instead the value was inserted as an empty string.
>
> How can I enforce that only the enum values are entered - i.e. receive an
> error when attempting to enter a non enum type value?

FWIW, I enforce it in php at point of input - so a drop down list or
radio button in a form for instance.

Dang - I'm at MySQL 4.1.10.

Triggers, constraints - 5.x features. Alright now I know what I need to do
:-)

Thx!

"Peter H. Coffin" <hellsop@ninehells.com> wrote in message
news:slrneur15g.7a4.hellsop@abyss.ninehells.com...
> On Tue, 6 Mar 2007 08:26:52 -0500, booner wrote:
>> So I setup a field as an enum data type:
>>
>> field1 enum('a','b','c')
>>
>> So when I tried to insert a value 'd' I was surprised when I did not
>> receive
>> an error - and instead the value was inserted as an empty string.
>>
>> How can I enforce that only the enum values are entered - i.e. receive an
>> error when attempting to enter a non enum type value?
>
> Constraint against a table that contains the acceptable values.
>
> --
> It is odd, but on the infrequent occasions when I have been called upon
> in a formal place to play the bongo drums, the introducer never seems to
> find it necessary to mention that I also do theoretical physics. --Feynman

booner wrote:
> So I setup a field as an enum data type:
>
> field1 enum('a','b','c')
>
> So when I tried to insert a value 'd' I was surprised when I did not receive
> an error - and instead the value was inserted as an empty string.
>
> How can I enforce that only the enum values are entered - i.e. receive an
> error when attempting to enter a non enum type value?
>
>
>
Take a look at:

http://dev.mysql.com/doc/refman/4.1/...aint-enum.html

and

http://dev.mysql.com/doc/refman/4.1/en/enum.html

....you should be able to specify the field with NOT NULL to force to to
default to the first enum entry.

Norm