Using enum

[Sonic]

I have an empty table created, however fields that were supposed to be enum, are instead tinytext.

I have tried to alter the table but I keep getting this error message:

Error # 1064

You have an error in your SQL syntax; check the manual for corresponds to your MySQL server version for the right syntax to use near 'NULL , change `New` `New` tinytext NULL' at line 1.

I get a similar message if I try to re-create the table with the fields as enum.

The only data I am filling in for the enum fields is the field name and field type (enum), I am leaving the length blank as well as the default blank and none of the check boxes are checked. I have tried Length 1 and Default set to N or No, but the error message would still appear.

Any ideas or help would be appreciated.

Charles

[peterlaursen]

this sql should work (it does here)

create table `test`.`TableName` (`ColumnName` ENUM('Y', 'N') NULL);

or

alter table `test`.`TableName1` ,change `ColumnName` `ColumnName` enum ('Y','N') NULL ;

(or NOT NULL with a default value)

With the sqlyoug GUI it looks like the image below . Note where to use quotes and not!

the sql corresponding to the image is

create table `test`.`enumtest` ( `qwer` enum ('Y','N') DEFAULT 'N' NOT NULL )

[peterlaursen]

And note the diffent quotation marks, see image

alter table `test`.`testtable` ,change `myenumtest` `mynewenumtest` enum ('Y','N') DEFAULT 'N' NOT NULL

[peterlaursen]

Sorry – that went wrong , se image

[peterlaursen]

this one works too (omitting the ” `” -character)

alter table test.testtable , change myenumtest myenumtest enum ('Y','N') DEFAULT 'N' NOT NULL

– but you won't have to worry about that stuff if you use the sqlyog GUI

[peterlaursen]

I see now that in my answer 11:42 I mixed up a picture showing an ALTER statement and some SQL containing a CREATE statement. Hope it doesn't confuse too much 😮

Further more I can tell now that SET variable types are treated just like ENUM's from the sqlyog GUI ….

[peterlaursen]

>> Ritesh

I it could be done it would be nice if the column header “LENGTH” of the CREATE TABLE and ALTER TABLE pane changed to “VALUES” when an ENUM or a SET variable is chosen as datatype. It also is not quite logical that in column LENGTH ” ' ” quotes MUST be given and in DEFAULT colum ” ' ” quotes MAY NOT …

Just a parsing detail …

I know people don't use those datatypes very much (but they should!) 😛

[Ritesh]

Thats a nice suggestion.

I will forward it to my development team.

[Author]

Posts