+ Reply to Thread
Page 3 of 6 FirstFirst 1 2 3 4 5 ... LastLast
Results 21 to 30 of 51

CREATE TABLE default data question

  1. Re: CREATE TABLE default data question

    Harald Fuchs wrote:

    > In article ,
    > Michael Austin writes:
    >
    >
    >>>>create table somename ( col1 varchar(20),
    >>>>col2 enum('0','1','2'));
    >>>>
    >>>>http://dev.mysql.com/doc/refman/5.0/en/enum.html
    >>>
    >>>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?

    >
    >
    >>Correct.

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


    %$@%$@#% 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
    DOCUMENTED. 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)

  2. Re: CREATE TABLE default data question

    Michael Austin wrote:

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


    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!


    > 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


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



    > Therefore using this
    > data-type, MySQL CANNOT GUARANTEE data integrity. Again, What in the
    > world were they thinking?


    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.


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


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


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


    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.



  3. Re: CREATE TABLE default data question

    Michael Austin wrote:

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


    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!


    > 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


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



    > Therefore using this
    > data-type, MySQL CANNOT GUARANTEE data integrity. Again, What in the
    > world were they thinking?


    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.


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


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


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


    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.



  4. Re: CREATE TABLE default data question

    Sanders Kaufman wrote:
    > Jerry Stuckle wrote:
    >
    >> Sanders Kaufman wrote:
    >>
    >>> 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']
    >>> )

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

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


    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.
    jstucklex@attglobal.net
    ==================

  5. Re: CREATE TABLE default data question

    Sanders Kaufman wrote:
    > Jerry Stuckle wrote:
    >
    >> Sanders Kaufman wrote:
    >>
    >>> 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']
    >>> )

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

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


    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.
    jstucklex@attglobal.net
    ==================

  6. Re: CREATE TABLE default data question

    Jerry Stuckle wrote:

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


    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.

  7. Re: CREATE TABLE default data question

    Jerry Stuckle wrote:

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


    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.

  8. Re: CREATE TABLE default data question

    Sanders Kaufman wrote:
    > Jerry Stuckle wrote:
    >
    >> 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.

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


    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.
    jstucklex@attglobal.net
    ==================

  9. Re: CREATE TABLE default data question

    Sanders Kaufman wrote:
    > Jerry Stuckle wrote:
    >
    >> 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.

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


    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.
    jstucklex@attglobal.net
    ==================

  10. Re: CREATE TABLE default data question

    In article ,
    Sanders Kaufman writes:

    > Michael Austin wrote:
    >> 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 DOCUMENTED. 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.


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


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

    > 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!


    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.

+ Reply to Thread
Page 3 of 6 FirstFirst 1 2 3 4 5 ... LastLast