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)

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.

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.

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

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

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.

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.

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

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

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.