Professional Web Applications Themes

CREATE TABLE default data question - MySQL

When you create a table, what's the syntax for setting default values. For example (pseudo): CREATE TABLE mytable ( id INT PRIMARY KEY AUTO_INCREMENT, myoption DEFAULT ['option1', 'option2'] )...

  1. #1

    Default CREATE TABLE default data question

    When you create a table, what's the syntax for setting default
    values.

    For example (pseudo):
    CREATE TABLE mytable (
    id INT PRIMARY KEY AUTO_INCREMENT,
    myoption DEFAULT ['option1', 'option2']
    )
    Sanders Guest

  2. #2

    Default Re: CREATE TABLE default data question

    Sanders Kaufman wrote: 

    I'm not sure what you're trying to do. What is the type of "myoption"?
    And a column can have only one default value.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: CREATE TABLE default data question

    Jerry Stuckle wrote: 
    >
    >
    > I'm not sure what you're trying to do. What is the type of "myoption"?
    > And a column can have only one default value.
    >[/ref]


    A DEFAULT value can only be *one* value (not a list of values). If you want to
    make sure only certain values are in a field, then you want an "enumerated"
    field or a constraint. See the docs for each method.

    --
    Michael Austin.
    Database Consultant
    Michael Guest

  4. #4

    Default Re: CREATE TABLE default data question

    Jerry Stuckle wrote: 
    >
    > I'm not sure what you're trying to do. What is the type of "myoption"?
    > And a column can have only one default value.[/ref]

    The type doesn't matter.
    I just want something (logically) like "DEFAULT IN (a, b, c)".

    I'm not sure, but I think some folks call in an ENUM?
    Sanders Guest

  5. #5

    Default Re: CREATE TABLE default data question

    Michael Austin wrote: [/ref]
     [/ref][/ref]
     [/ref]
     

    Thanks Michael.
    I looked up "enumerated" and "constraint" but then drowned in
    the results.
    Can you narrow it down? I think, without any sense of knowing,
    that it has something to do with creating an ENUM.
    Sanders Guest

  6. #6

    Default Re: CREATE TABLE default data question

    Sanders Kaufman wrote:
     
    >>
    >>
    >> I'm not sure what you're trying to do. What is the type of
    >> "myoption"? And a column can have only one default value.[/ref]
    >
    >
    > The type doesn't matter.
    > I just want something (logically) like "DEFAULT IN (a, b, c)".
    >
    > I'm not sure, but I think some folks call in an ENUM?[/ref]


    Now we need to narrow down your question just a bit - the answer depends on how
    you answer the following:

    1) do you want to restrict the values entered into this table?
    example:
    create table somename ( col1 varchar(20),
    col2 enum('0','1','2'));

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

    2) if no value is specified, do you want it to DEFAULT to some value?
    see example below
    if yes, what single value do you want as a default?

    example:
    mysql> create table somename ( col1 varchar(20),
    col2 enum('0','1','2') default '0');
    Query OK, 0 rows affected (2.19 sec)

    ***NOTE*** I do not specify col2 and the default value is '0'
    mysql> insert into somename (col1) values ('test'),('test1');
    Query OK, 2 rows affected (0.14 sec)
    Records: 2 Duplicates: 0 Warnings: 0

    mysql> select * from somename;
    +-------+------+
    | col1 | col2 |
    +-------+------+
    | test | 0 |
    | test1 | 0 |
    +-------+------+


    --
    Michael Austin.
    Database Consultant
    Michael Guest

  7. #7

    Default Re: CREATE TABLE default data question

    Michael Austin wrote: 
     
    >
    > Now we need to narrow down your question just a bit - the answer depends
    > on how you answer the following:
    >
    > 1) do you want to restrict the values entered into this table?
    > example:
    > create table somename ( col1 varchar(20),
    > col2 enum('0','1','2'));
    >
    > http://dev.mysql.com/doc/refman/5.0/en/enum.html[/ref]


    BAM! That's it! I asked the wrong question at the start. I
    didn't want "default" value, per se - I just wanted that
    constraint in there. That is what that does, right?

    So like...

    colGender enum('male', 'female') DEFAULT 'male',

    would limit the possible values to male and female
    , defaulting to male - right?

    Sanders Guest

  8. #8

    Default Re: CREATE TABLE default data question

    Sanders Kaufman wrote:
     
    >

    >>
    >>
    >> Now we need to narrow down your question just a bit - the answer
    >> depends on how you answer the following:
    >>
    >> 1) do you want to restrict the values entered into this table?
    >> example:
    >> create table somename ( col1 varchar(20),
    >> col2 enum('0','1','2'));
    >>
    >> http://dev.mysql.com/doc/refman/5.0/en/enum.html[/ref]
    >
    >
    >
    > BAM! That's it! I asked the wrong question at the start. I didn't
    > want "default" value, per se - I just wanted that constraint in there.
    > That is what that does, right?
    >
    > So like...
    >
    > colGender enum('male', 'female') DEFAULT 'male',
    >
    > would limit the possible values to male and female
    > , defaulting to male - right?
    >[/ref]

    Correct.

    But for these fields I would use CHAR(1) 'M','F' a little less wasted space.
    --
    Michael Austin.
    Michael Guest

  9. #9

    Default Re: CREATE TABLE default data question

    In article <FfDnh.28952$news.prodigy.net>,
    Michael Austin <com> writes:
     
    >> BAM! That's it! I asked the wrong question at the start. I didn't
    >> want "default" value, per se - I just wanted that constraint in
    >> there. That is what that does, right?
    >> So like...
    >> colGender enum('male', 'female') DEFAULT 'male',
    >> would limit the possible values to male and female
    >> , defaulting to male - right?[/ref][/ref]
     

    Not quite. It's not a constraint and does not prevent you from
    entering 'dunno' into colGender - in this case MySQL silently changes
    the entered values to something else.

    If you need real constraints, use some other DBMS (e.g. PostgreSQL).
    Harald Guest

  10. #10

    Default Re: CREATE TABLE default data question

    Harald Fuchs wrote: 
     [/ref][/ref]
     [/ref]
     

    "Silently changes the entered values"?!
    I would have thought the insert or update would fail in that case.

    Is that true, Michael?
    Sanders Guest

  11. #11

    Default Re: CREATE TABLE default data question

    Harald Fuchs wrote:
     [/ref]
    >

    >
    >
    > Not quite. It's not a constraint and does not prevent you from
    > entering 'dunno' into colGender - in this case MySQL silently changes
    > the entered values to something else.
    >
    > If you need real constraints, use some other DBMS (e.g. PostgreSQL).[/ref]

    %$%$#% Obviously I did not fully test this before recommneding it :(


    Most databases - when using an enumerated type field, you should receive an
    error in the case where it was not one of the enumerated values. Enumerated
    fields should also return an error (IIRC, and I could be wrong, but I think my
    old V4.1 did). I just tested my 5.1 and it changes the value to '' and enters
    the rest of the record. I would consider this a bug. REGARDLESS OF HOW IT IS
    DOENTED. It should not insert invalid data - changing values from something
    to 0 is plain wrong. ENUM *should* functionally be a check constraint. If not,
    what real purpose does it serve? None!!!

    The more I use MySQL, the more it is apparent that those writing the code do not
    fully consider the ramifications of their actions.

    In this case, it makes ENUM of no value. If I can store "anything" - even
    though it gets converted to x0 -essentially a NULL value and even though you add
    NOT NULL to this column - it should return ERROR - not Warning. PERIOD!!!
    What in the world were they thinking? This breaks all sorts of rules for
    maintaining data atomicity and consistency which directly affects data
    integrity. The data I think I stored is not the data that mysql actually stored.
    Therefore when I go looking for the data I think I stored - it will not be
    there. Therefore using this data-type, MySQL CANNOT GUARANTEE data integrity.
    Again, What in the world were they thinking?

    Same for the ANSI standard data type definitions. Take the CHAR datatype. CHAR
    should right-pad the field with trailing spaces in the case where the data is
    shorter than the column definition - and it does store it that way. When I
    SELECT data from that field - I should get it **with** the trailing spaces.
    Otherwise it is functionally no different that the VARCHAR data type - which
    essentially makes data-types moot.

    So, the only way to fix your problem is to have a table for your enumerated
    values and have a foreign key constraint on this column that points to the other
    table... Or, have your web form do data validation - which it probably should
    anyway...

    More reasons why, as a technology "gatekeeper" at a very large global company,
    MySQL WILL NOT be used at the company for which I work. I may use it for small
    home projects, but not for real work.

    --
    Michael Austin.
    Database Consultant

    here is an example of how WRONG the CHAR datatype functions which demonstrates a
    violation of data integrity. What I stored is not what I got back:

    mysql> insert into b values ('ABC ');
    Query OK, 1 row affected (0.15 sec)

    mysql> select '~'||b||'~' from b;
    +-------------+
    | '~'||b||'~' |
    +-------------+
    | ~ABC~ |
    +-------------+
    4 rows in set (0.01 sec)
    Michael Guest

  12. #12

    Default Re: CREATE TABLE default data question

    Michael Austin wrote:
     

    That's the case with most Open Sores applications.
    It starts with some guys and gals with the kernel of an idea.
    Then it grows like a fractal.

    MySQL has a LOT of weirdness to it - but it is *great*.
    I'll take little quirks like this over a multi-thousand dollar
    price tag any day.

    That said - I can't believe they did this!

     

    Warning?
    I know about getting errors - but "warning"?
    Is that something MySQL does?


     

    Well, yeah they can by refusing to accept bad data

    What's lost in catching programmer errors is made up for in
    speed, performance, scalability and expandability.

     

    Indeed - the complexities of processing the data is best left on
    the client.

     

    I can certainly see why, at a large global company, you need a
    more baby-sitter type of database server. With a zillion
    different clients of varying reliability, and almost no
    accountability - the server has to be rugged like a dune buggy.

    What's lost in speed, performance, scalability and expandability
    is gained by catching programmer errors.


    Sanders Guest

  13. #13

    Default Re: CREATE TABLE default data question

    Sanders Kaufman wrote: 
    >>
    >>
    >> I'm not sure what you're trying to do. What is the type of
    >> "myoption"? And a column can have only one default value.[/ref]
    >
    >
    > The type doesn't matter.
    > I just want something (logically) like "DEFAULT IN (a, b, c)".
    >
    > I'm not sure, but I think some folks call in an ENUM?[/ref]

    If you use INNODB you can also create a table of valid values then use a
    foreign key constraint to limit the values to those which are in the
    second table.

    It doesn't work for MyISAM, though.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  14. #14

    Default Re: CREATE TABLE default data question

    Jerry Stuckle wrote:
     

    Yeah - there's a lot of stuff you can do with the third-party
    engines. But the web sites I've worked with always disable them.
    Sanders Guest

  15. #15

    Default Re: CREATE TABLE default data question

    Sanders Kaufman wrote: 
    >
    >
    > Yeah - there's a lot of stuff you can do with the third-party engines.
    > But the web sites I've worked with always disable them.[/ref]

    Maybe you need to find other hosts. I don't have that problem. But I
    generally work with VPS's.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  16. #16

    Default Re: CREATE TABLE default data question

    In article <MQTnh.60522$news.prodigy.net>,
    Sanders Kaufman <net> writes:
     [/ref]
     

    PostgreSQL is also open source (actually, more "open" than MySQL) and
    has a different history.
     
     

    Well, it's just one of the MySQL Gotchas
    (http://sql-info.de/mysql/gotchas.html) - they might hurt you if you
    don't know them.
    Harald Guest

  17. #17

    Default Re: CREATE TABLE default data question

    Jerry Stuckle wrote: 
     
    >
    > Maybe you need to find other hosts. I don't have that problem. But I
    > generally work with VPS's.[/ref]

    That would be failing to do my job.
    My job is to make what they have work.
    Sanders Guest

  18. #18

    Default Re: CREATE TABLE default data question

    Harald Fuchs wrote: 
     
    >
    > PostgreSQL is also open source (actually, more "open" than MySQL) and
    > has a different history.[/ref]

    Unfortunately, my Zend Studio and other applications have only
    the mildest support for Postgre, if at all.
    Sanders Guest

  19. #19

    Default Re: CREATE TABLE default data question

    Sanders Kaufman wrote: 
    >

    >>
    >>
    >> Maybe you need to find other hosts. I don't have that problem. But I
    >> generally work with VPS's.[/ref]
    >
    >
    > That would be failing to do my job.
    > My job is to make what they have work.[/ref]

    Actually, I find my job is to make the customer happy. And sometimes
    that means finding a different host.

    I've changed a lot of customers to different hosts. Even saved them a
    bunch of money at times.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  20. #20

    Default Re: CREATE TABLE default data question

    Jerry Stuckle wrote: 
     
    >
    > Actually, I find my job is to make the customer happy. And sometimes
    > that means finding a different host.
    >
    > I've changed a lot of customers to different hosts. Even saved them a
    > bunch of money at times.[/ref]

    That's scope creep as far as I'm concerned.
    I'm a tech guy, not a business consultant.

    Mixing the two is waaay too political for me.
    I'm more of a "Would you like fries with that?" kinda guy.
    Sanders Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 0
    Last Post: June 1st, 03:15 AM
  2. Create table question
    By Dave in forum MySQL
    Replies: 3
    Last Post: December 14th, 07:51 AM
  3. question: how to preload data and excute table creation scripts
    By Michael Fuhr in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 11th, 07:32 PM
  4. table data question
    By The Lone Wolf in forum PHP Development
    Replies: 4
    Last Post: August 21st, 07:55 AM
  5. Create Table and Boolean Data Type
    By Erland in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 21st, 02:52 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