Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jun 2003
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using enum in table

    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.

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What was the actual query you used?

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Don't use ENUM at all. Use a type of TINYINT and store 1 for yes or 0 for no in the field.

  • #4
    Regular Coder
    Join Date
    Jun 2003
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Inside the database, I went to the structure of the table and did a add new field.

    Is there an easier way?

    Thanks.

    jls

  • #5
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #6
    Regular Coder
    Join Date
    Jun 2003
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, I can use tinyint, but I'm not sure what you mean...

    store 1 for yes or 0 for no in the field

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #8
    Regular Coder
    Join Date
    Jun 2003
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, I wanted to use yes/no.

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •