Thank you Michael for your reply. Ok, at least there is a good reason and now I too understand the reason behind it.
I have to admit that even if I can remember having earlier read that part you quoted I never really "understood" what there was written since the logic behind this just didn't make sense to me. But does anybody know if this is to be changed in the future for the transactional db formats now ones supported ? Michael, maybe your'e right about the client program having to do the validation. But as (having) relied heavily on booleans, lists etc combined with defining not null:s in the columns I'm somewhat shaken at the moment. MySQL doesn't put in empty strings in int type columns, I was merely just so upset I managed to get more of my test columns with varchar type. Jan > -----Original Message----- > From: Michael Stassen [mailto:[EMAIL PROTECTED] > Sent: Saturday, November 01, 2003 05:13 > To: Jan Magnusson > Cc: Mysql General mailing list > Subject: Re: "not null" and default values confusion > > > Jan, > > This question comes up a lot. You should take a look at > <http://www.mysql.com/doc/en/constraint_NOT_NULL.html>. The first line > is, "To be able to support easy handling of non-transactional tables, > all fields in MySQL have default values." Mysql automatically converts > missing or illegal values to column defaults, because in the > non-transactional case, it cannot roll back when you have an illegal > value in the Nth row of a multi row insert. > > The solution is to have the program/client verify data before inserting, > updating, or loading the db, rather than counting on mysql to reject bad > data. After all, there are a lot of ways for data to technically fit a > column definition but still be bad in terms of your application. If you > can't trust users not to leave a field blank, can you trust them not to > enter nonsense? As long as you're validating input anyway, making sure > it's not NULL shouldn't be a big deal. > > You can turn off defaults for single row inserts by building your own > mysql from source with the -DDONT_USE_DEFAULT_FIELDS compile option, in > which case insert statements will "generate an error unless you > explicitly specify values for all columns that require a non-NULL > value." But if I understand correctly, this turns off using any > defaults, even those you've explicitly set in column definitions. > (Someone please correct me if I'm wrong.) > > Finally, I'd be shocked if mysql stored an empty string in an int > column. Perhaps mysqlcc got confused? > > Michael > > Jan Magnusson wrote: > > Dear List, > > > > > > Could sombody explain this behaviour of mySQL 4 to me: > > > > Assigning a column as "not null" will AUTOMATICALLY assign > (=force upon) it > > a default value of an empty string ('') if a string or zero (0) if a > > numerical datatype. Thereby effectively bypassing all and every error > > message during inserts if missing values in records. > > > > Why ??? > > > > - This behaviour in effect means there will never be generated an error > > during insertion and omitting a column assigned as "not null" > because mysql > > will on its own generate a default value for it. > > > > - What use do I have for the "not null" constraint on the column if it's > > anyway assigned a value. As it now works it's the same as > forcing a default > > value attribute. > > > > - At first I understood the mySQL documentation (6.3.5.) so > that if you just > > omit the value but still having the default attribute specified in your > > CREATE TABLE... it would then automatically give it a "default default > > value". Makes sense. > > > > - This does not seem to apply to PK:s, but all other columns. > > > > BTW, when trying this out with mysqlcc I also managed to generate output > > where the default value generated by mysql for an int data type column > > contrary to documentation too was an empty string instead of "0". > > > > Am I now missing something obvious in the logic of this behaviour ??? > > > > Jan > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
