Professional Web Applications Themes

Enum data type - MySQL

So I setup a field as an enum data type: field1 enum('a','b','c') So when I tried to insert a value 'd' I was surprised when I did not receive an error - and instead the value was inserted as an empty string. How can I enforce that only the enum values are entered - i.e. receive an error when attempting to enter a non enum type value?...

  1. #1

    Default Enum data type

    So I setup a field as an enum data type:

    field1 enum('a','b','c')

    So when I tried to insert a value 'd' I was surprised when I did not receive
    an error - and instead the value was inserted as an empty string.

    How can I enforce that only the enum values are entered - i.e. receive an
    error when attempting to enter a non enum type value?


    booner Guest

  2. #2

    Default Re: Enum data type

     
    receive 

    ENUM doesn't enforce a check constraint.

    It might be depending on some obscure setting though :-)

    Alternatively, use triggers to force the value.


    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  3. #3

    Default Re: Enum data type

    On 6 Mar, 13:26, "booner" <com> wrote: 

    Maybe you had the field set to allow null values

    Captain Guest

  4. #4

    Default Re: Enum data type

    On 6 Mar, 13:26, "booner" <com> wrote: 

    Mid you, when I just tried it I got

    #1265 - Data truncated for column 'field1' at row 1

    Captain Guest

  5. #5

    Default Re: Enum data type

    On Mar 6, 1:26 pm, "booner" <com> wrote: 

    FWIW, I enforce it in php at point of input - so a drop down list or
    radio button in a form for instance.

    strawberry Guest

  6. #6

    Default Re: Enum data type

    On Tue, 6 Mar 2007 08:26:52 -0500, booner wrote: 

    Constraint against a table that contains the acceptable values.

    --
    It is odd, but on the infrequent occasions when I have been called upon
    in a formal place to play the bongo drums, the introducer never seems to
    find it necessary to mention that I also do theoretical physics. --Feynman
    Peter Guest

  7. #7

    Default Re: Enum data type

    On 6 Mar 2007 07:05:40 -0800, strawberry wrote:
     
    >
    > FWIW, I enforce it in php at point of input - so a drop down list or
    > radio button in a form for instance.[/ref]

    Bonus for the contraint method is populating those dropdowns becomes a
    dynamic thing pulled from the constraining table, with only one place
    to change values... (Yes, I've gotten bitten by that one before. Other
    developer had used no less than *four* different methods of populating
    selection widgets. I ended up having to search the entire source tree
    for all the explicit values to mop it up.)

    --
    Compared to system administration, being cursed forever is a step up.
    -- Paul Tomko
    Peter Guest

  8. #8

    Default Re: Enum data type

    Dang - I'm at MySQL 4.1.10.

    Triggers, constraints - 5.x features. Alright now I know what I need to do
    :-)

    Thx!

    "Peter H. Coffin" <com> wrote in message
    news:ninehells.com... 
    >
    > Constraint against a table that contains the acceptable values.
    >
    > --
    > It is odd, but on the infrequent occasions when I have been called upon
    > in a formal place to play the bongo drums, the introducer never seems to
    > find it necessary to mention that I also do theoretical physics. --Feynman[/ref]


    booner Guest

  9. #9

    Default Re: Enum data type

    On Tue, 6 Mar 2007 12:10:11 -0500, booner wrote: 

    http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html

    14.2.7.4. FOREIGN KEY Constraints

    Starting from MySQL 3.23.44, InnoDB features foreign key constraints.

    --
    It's not hard, it's just asking for a visit by the up fairy.
    -- Peter da Silva
    Peter Guest

  10. #10

    Default Re: Enum data type

    booner wrote: 
    Take a look at:

    http://dev.mysql.com/doc/refman/4.1/en/constraint-enum.html

    and

    http://dev.mysql.com/doc/refman/4.1/en/enum.html

    ....you should be able to specify the field with NOT NULL to force to to
    default to the first enum entry.

    Norm


    Norman Guest

Similar Threads

  1. sort on column type enum
    By vjyanand@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 27th, 07:37 AM
  2. probleme UPDATE data de type ENUM
    By Yves in forum MySQL
    Replies: 4
    Last Post: February 21st, 07:40 AM
  3. Converting from data type varchar to data type money
    By schaudry in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 10th, 11:01 PM
  4. Converting data type varchar to data type money
    By schaudry in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: June 10th, 07:22 PM
  5. Replies: 2
    Last Post: October 16th, 08:29 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139