View Full Version : Using enum in table

08-15-2006, 01:26 PM
I'm trying to add a new field to an existing table. I would like to add a simple yes/no in the field.

here's what I did:

field: eligible
type: enum
null: not null

I'm getting this error: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL' at line 1

What am I doing wrong?

Thank you.

Kid Charming
08-15-2006, 04:07 PM
What was the actual query you used?

08-15-2006, 04:36 PM
Don't use ENUM at all. Use a type of TINYINT and store 1 for yes or 0 for no in the field.

08-15-2006, 04:37 PM
Inside the database, I went to the structure of the table and did a add new field.

Is there an easier way?



Kid Charming
08-15-2006, 04:40 PM
You interact with the database with queries. It sounds like you're using a client, which is just generates a query from whatever options you pick in the GUI. There should be a window somewhere that shows then entire query the client generated and sent to the database. We need to see that query.

For more info on adding columns via query, read the man page on ALTER TABLE (http://dev.mysql.com/doc/refman/5.0/en/alter-table.html).

08-15-2006, 04:42 PM
OK, I can use tinyint, but I'm not sure what you mean...

store 1 for yes or 0 for no in the field

08-15-2006, 08:43 PM
You wanted to use ENUM so that the only choices could be Yes or No correct? Instead I'm suggesting you use a type of INT and then only use 1 or 0 to represent a choice of Yes or No in the field.

There are numerous problems with using ENUM including the following as provided in the mysql manual itself:

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.

08-15-2006, 11:21 PM
Yes, I wanted to use yes/no.

08-16-2006, 12:40 AM
Then ignore my advice, create an ENUM column and use YES/NO, just realize that while using ENUM your data can become flawed because of irregularities in the data used.