...

View Full Version : Using enum in table



jlsohio
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
length/values:
attributes:
null: not null
default:
extra:

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?

guelphdad
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.

jlsohio
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?

Thanks.

jls

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).

jlsohio
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

guelphdad
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.

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

guelphdad
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum