I want to know the differences between the following 3 lines of mysql code.
ct_session_id CHAR(32) NOT NULL DEFAULT ’ ',
ct_session_id CHAR(32) NOT NULL DEFAULT ‘0’,
ct_session_id CHAR(32) NOT NULL,
I was going through a tutorial where I saw the part, DEFAULT ’ '. Now in my phpmyadmin page, I did not know whether to leave the default column blank or do something else.
The first sets the field to a blank if you don’t specify a value.
The second sets it to zero of you don’t specify a value.
The third crashes if you don’t specify a value.
This one always drives me nuts:
ct_session_id CHAR(32) NOT NULL DEFAULT ' '
why create something and say it has to have a value (by declaring it not null), but then arbitrarily assigning an empty string in the case a value isn’t entered.
that isn’t an empty string
and anyway, you ~can’t~ default a CHAR column to an empty string
(say, haven’t we had this conversation before?)
It shouldn’t drive you nuts since they are two totally different things.
Assigning a field a value of blank means the field has a value of blank. The field is clearly specifying that the value is known to be blank rather than having any other value.
Allowing a field to be NULL means you don’t know what the value of the field is - it could be supposed to be blank or something else but you don’t know.
For example consider a field to contain someone’s middle name. Giving the field a value of blank indicates that they do not have a middle name (or have specifically decided that they are not going to supply it). If you don’t know if they have a middle name then blank is not an appropriate value for the field and NULL should be used. If you actually ask them to supply their middle name before generating the record then the value for the field should always be known and NULL is then an inappropriate value while blank is a perfectly reasonable value.
sorry, stephen, i’m afraid i have to agree with guelphdad
DEFAULT ’ ’ is ludicrous with NOT NULL
Thinking about it I agree too since you’d need to be able to pass in the value if it is known and so can pass the blank if that is the appropriate value rather than setting it as a default.
technically, i suppose you could pass in a blank for a NOT NULL string column
but logically, that doesn’t make sense to me
and yes, i did read post #5
it still doesn’t make sense to me
So what would you pass for a middle name field for someone who doesn’t have a middle name? Since the field will not accept and empty string you need to have a value that means there is no middle name and a single blank is probably as good a choice as any.
“since the field will not accept an empty string” is misleading
the fact that it will accept spaces is also wrong
if you’re asking what ~i~ would do, i would simply allow NULL
and i’m not prepared to discuss it further, because the whole business of needing to distinguish between known versus non-existent is a red herring in my opinion
But that would just allow for the situation where you don’t know what the person’s middle name is. You still need to set it to a specific value when you do know what it is and when you know that what it is is nothing at all since they haven’t got one then the correct value to set it to would be an empty string. Setting it to a single space if an empty string is invalid is then the most appropriate compromise.
Then you can distinguish between someone who will not supply their middle name and someone who does supply the fact that they don’t have one since the unknown middle name is NULL and the haven’t got one is ’ '.
Being able to distinguish whether the value is known or not is the purpose of NULL. NULL should never be used when the value is known and should always be used when it is unknown.
If you decide that you don’t need to distinguish unknown fields (which in any event is jumbling what you know about the value of a field with its actual value) then you can abandon NULL completely and avoid all the problems NULL can cause.
A good book that goes into lots of detail on why using NULL is a bad idea and why the relational model ought to never have been altered to include it is “SQL and Relational Theory” by C.J.Date (yes THE C.J.Date)
I have to agree with Rudy, NULL is better if a person does not have a middle name then there is no value, ie it’s NULL.
Not at all - NULL in SQL stands for UNKNOWN not for an empty string and totally screws up all the logic by adding a third state that is totally irrelevant to the real use of the field (since it reflects what you know about the fields value rather than what the value is). Since adding NULL into any database immediately changes you from true/false logic to true/false/unknown logic you should do everything possible to avoid using NULL anywhere.
Their middle name isn’t unknown therefore the one thing you can say about it is that it is NOT NULL. From there you just need to decide what value to use to represent an empty string.
I already mentioned a book by THE authority on relational databases that goes into great detail on why NULL ought never to have been added in the first place and certainly ought never to be ever even considered for actual use.
like i said, i am not going to discuss it further
this is just plain wrong –
and when you know that what it is is nothing at all since they haven’t got one then the correct value to set it to would be an empty string
like i said, i don’t agree
and i ~especially~ don’t want to discuss it with you after you first say this –
NULL should never be used when the value is known and should always be used when it is unknown.
and then follow it up with this –
Since adding NULL into any database immediately changes you from true/false logic to true/false/unknown logic you should do everything possible to avoid using NULL anywhere.
have a look here: Nulls - A Socratic Enquiry
In sum, it is proposed that the definition of “null” as “improper value” solves, with minimal theoretical adjustment, the major problems of nulls, and explains much about alternative unacceptable solutions. More importantly, it is proposed that any alternative treatment of nulls should (1) be at least as satisfactory at explaining and solving the problems of nulls, (2) require no more complex theoretical adjustment, and (3) include, and preferably start with, an answer to the Socratic question: What is a null?
multiple decades of experience with SQL has shaped my opinion – i heart NULL and am quite happy to use it, so please stop trying to convince me otherwise… it’s not going to happen
Sorry, but I am quite a newbie in this matter and have been reading through the discussion by the pros and trying to understand whatever I can. I still have some very newbie doubts.
od_shipping_first_name VARCHAR(50) NOT NULL,
od_shipping_last_name VARCHAR(50) NOT NULL DEFAULT ‘’
,
Does the above mean that the first name (od_shipping_first_name) should necessarily have a value? So, no default value has been set. But the last name (od_shipping_last_name) may or may not have a value. Hence, a default value of ’ ’ has been set.
But then I think I am wrong in my thinking because default values of ’ ’ have been set for the shipping address, postal code etc. which should necessarily have values, isn’t it? (As you have guessed, I am learning how to make a shopping cart).
For the product quantity, the default value has been set to 0 (pd_qty SMALLINT UNSIGNED NOT NULL DEFAULT 0). That means some product quantity can have the value 0 (if the buyer does not want to buy that product) and some other product quantity can have whatever value the buyer wants. But then in what way is it different from setting the default value to ’ '. Or does a value like 0 apply to numeric values and a value like ’ ’ is for something containing characters? Am I right?
There are some places like the order id, where absolutely no default value has been set. The code is just, od_id INTEGER UNSIGNED NOT NULL. Does this mean that the order id (od_id) will definitely have a value and hence has been set to NOT NULL. Am I right here? I hope my newbie questions are clear enough.
Probably, the threads above cover many of my doubts but it is just that I am too much of a newbie to be able to understand everything.
multiple decades of experience with SQL has shaped my opinion – i never use NULL and am quite happy to always use the alternative, so please stop trying to convince me otherwise… it’s not going to happen
I guess we have had different experiences of it. Let’s just agree to disagree.
you “never” use NULL? just a couple of posts ago, you were advising people “NULL should never be used when the value is known and should always be used when it is unknown.”
inconsistency really detracts from your credibility, todd
There should also never be any situation where the value is unknown except for a short period while you are modifying a database and haven’t collected the new information yet.
So during the modification process I would add the new field allowing NULL and then add the values to all the entries and then update it again to NOT NULL. So by the time the field is ready to be used it would be NOT NULL since by then all the values are known.
So I should have said that I never use NULL with live data since once the data has actually been collected and entered in the database none of it is unknown and so none of it should be represented by NULL.
For anyone who does use NULL in their data consider when you have a comparison where (A <> B) OR (B <> C) and where A anc C are known values that are not equal and B is NULL - where the query will therefore NOT return the expected results.
dream on, josé 
(btw: nice backpedal)
oh wait… you stick “dummy values” like empty strings and spaces in your data
yup, you win
i’m going to go kill myself now
Now that you guys mention it, I can’t say I’ve ever used NULL except in special circumstances. For example, when dealing with more than one field being used to define a primary or unique key.
Otherwise, if it’s just stored data, using NULL tends to add unnecessary complexity. Just checking for a known datatype–even if it’s an empty string or a zero–is easier to handle and wouldn’t impact data integrity in most cases.