INSERTing a default ENUM value

[databasis]

Hello,

Although the MYSQL documentation states that there are default values for all its datatypes, whenever I specify a default value for an ENUM type, and then use "DEFAULT" in my INSERT query, when the row is created, the ACTUAL value stored is '0' and not the default value. Any ideas why this might not be working?

I did use NOT NULL when defining the ENUM type so that the default value would be the first in the ENUM list. When I look at the DESCRIBE table data, the default value is clearly there.

Any suggestions appreciated.

Thanks

Maria

 

[sleipnir214]

This works for me:

create table foo (id int unsigned, test enum('Y', 'N') default 'N'); Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[databasis]

Hello and thank you for your prompt reply. I've redefined the table the way you said but EVEN after doing that the INSERT query will not work. If I insert values to this table, and instead of specifing 'N' explicitly, but typing "DEFAULT", it SHOULD enter 'N' in the record, but it doesn't. So does the INSERT query work for you, if you say "DEFAULT" instead of "N"?

 

[sleipnir214]

You're explicitly using "DEFAULT"? What won't work unless you are using MySQL version 4.0.3 or newer.

If you are running a version of MySQL lower then 4.0.3, just skip the column during the insert -- MySQL will automatically insert the default value for the unmentioned columns. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[databasis]

Hi,

The thing is, I AM using an earlier version of MySQL, but will all other INSERT queries, I explicitly used "DEFAULT" and it worked. It just won't work with the enum datatype. I'll use your suggestion, though. It's the best way to do it. Thanks, Maria

 

[r937]

i think the problem is your choice of ENUM as the datatype

if you have values FOO, BAR, and QUX and the default is QUX then i believe it stores a 2

the ENUM values work like an index into the array of values that they represent

if you specifically want Y and N, use CHAR(1)


rudy

 

[databasis]

Rudy,

You are right. Thank you. I've changed the datatype to CHAR(1) and it's grand now.

Maria