Enum data type - MySQL

[booner]

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?

[Martijn]

 
receive 

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

[Captain]

On 6 Mar, 13:26, "booner" <com> wrote: 

Maybe you had the field set to allow null values

[Captain]

On 6 Mar, 13:26, "booner" <com> wrote: 

Mid you, when I just tried it I got

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

[strawberry]

On Mar 6, 1:26 pm, "booner" <com> wrote: 

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

[Peter]

On Tue, 6 Mar 2007 08:26:52 -0500, booner wrote: 

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

[Peter]

On 6 Mar 2007 07:05:40 -0800, strawberry wrote:
 
>
> FWIW, I enforce it in php at point of input - so a drop down list or
> radio button in a form for instance.[/ref]

Bonus for the contraint method is populating those dropdowns becomes a
dynamic thing pulled from the constraining table, with only one place
to change values... (Yes, I've gotten bitten by that one before. Other
developer had used no less than *four* different methods of populating
selection widgets. I ended up having to search the entire source tree
for all the explicit values to mop it up.)

--
Compared to system administration, being cursed forever is a step up.
-- Paul Tomko

[booner]

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" <com> wrote in message
news:ninehells.com... 
>
> 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[/ref]

[Peter]

On Tue, 6 Mar 2007 12:10:11 -0500, booner wrote: 

http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html

14.2.7.4. FOREIGN KEY Constraints

Starting from MySQL 3.23.44, InnoDB features foreign key constraints.

--
It's not hard, it's just asking for a visit by the up fairy.
-- Peter da Silva

[Norman]

booner wrote: 
Take a look at:

http://dev.mysql.com/doc/refman/4.1/en/constraint-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