When to make mysql fields NULL and NOT NULL... I'm completely lost.

[aqw]

Hi!

When to make mysql fields NULL and NOT NULL... I'm completely lost.

Thank you for your help.

[webchick]

You make fields NOT NULL when a given item is required. You make them NULL when a given item is optional.

For example, say you had a customers table. You might set it up like this:

firstname = NOT NULL
lastname = NOT NULL
....
faxnumber = NULL

All customers should have a first and last name, but not everyone might have a fax number.

[aqw]

I know that very well.

BUT should I make the fax field NOT NULL because of the PERFORMANCE ISSUE (I'm only interested in the performance issue here).

thanks

[guelphdad]

No you should not make it not null. If there is no fax number and it is set to not null what do you do? If you allow for null you leave the field blank and it is assigned NULL value.

There is no benefit from performance in terms of speed in queries if that is what you are thinking.

[TheRedDevil]

Use NULL on all fields which might not contain a "value".

Also keep in mind NULL is a state, not a number. NEVER use NULL on a field that should contain a number!

[r937]

there is no performance issue

make your columns NULL or NOT NULL based on whether the value is mandatory

and i totally disagree with never using NULL on a number

let's rephrase it without so many negatives -- always use NULL for a number (or any datatype) if you need to have a value before allowing the insert of the row containing that column

would you reject entering a paying customer into your ecommerce system because they don't have a fax number?

and please, do not make the mistake of declaring NOT NULL but then assigning a DEFAULT value of zero or an empty string -- that's a slippery semantic slope and will cause integrity problems with numeric values

[TheRedDevil]

r937: Your right, I should have been more clear. What I meant was that you should not use NULL on fields where you will use the numbers in calculations. Like field for amount, quantity etc.

When thinking on it again, your sentence makes sence as long as you make sure it will never be submitted as blank into the query. I.e. fix the problem in the script, though Ive always felt it more secure not applying NULL to fields like that.

[r937]

What I meant was that you should not use NULL on fields where you will use the numbers in calculations. Like field for amount, quantity etc.

i'm sorry, i still disagree with this

if you're thinking of aggregate functions like SUM, they ignore nulls automatically, so there's nothing to worry about

if you're thinking of single-row expressions, like amount=quantity+937, then if one of the column values is null, the answer is null, and that is exactly what you want!!

[TheRedDevil]

r937:
As I mentioned before, Im starting to see it your way.
It seems like my teacher was not as good after all, as your pointing out something he said the oposite off.

I think I can think of one thing that will break it though. lets say you got a total value field, a field for the number of that item and the price. If you then suddenly got a few more items your adding to the database and want the total value calculated in the same query.
I.e. total_value=(item_nr+$more_items)*item_price
If Im not mistaken if the item_nr field and the item_price field is NULL the result will became the same, or?

Thanks

[r937]

sorry, i did not quite understand that last example

could you please illustrate it with a few sample rows?

[TheRedDevil]

Lets say we got a table "items" with these values:
id
item
item_nr
item_price
total_value

Then you have a script where you update the item number according to if you get more stock or sell a item. Lets take an example if you add more stock.

mysql_query("UPDATE items SET item_nr=item_nr+$nr_new_items, total_value=(item_nr+$nr_new_items)*item_price WHERE id=$item_id");

As I understand the way NULL works, the total_value will result in NULL if any of those values is NULL. We know for sure that the $nr_new_items is a number since its put in the script, but both item_nr or item_price might be NULL if the $variable that was supposed to keep that value was empty (i.e. false) when the item was added to the database. The main issue here is that if the item_nr was NULL then it would always stay NULL if you used a code like this to update it "item_nr=item_nr+$nr_new_items", hence it would always fail the total_value too.

At least this is how I think it would work. Please correct me if Im mistaken.

Thanks

[r937]

yes, that's correct

however, it is very consistent and makes perfect sense!!

if you have a row for a particular item id where item_nr is NULL, that means you don't know how many you have

then if you add 3 more, you still don't know how many you have!!!

i.e. NULL + 3 = NULL

as for total_value, you wouldn't need to store that column anyway

[TheRedDevil]

Thanks for your reply Rudy.

I see that your correct, I just done like the feeling when I use the NULL value on importent "number" fields. I always write proper checking on the client side before any input is added to the database, but sometimes clients tend to add/update info through a program like phpmyadmin. Though I guess that is not my liability

So you have converted one more to start using NULL on number values too.

Btw, if I can just ask you one more question please.

How does adding the NULL value to a field affect the speed of a query, taking the table got alot of rows stored in it? Since the NULL value applied adds one bit to every row in the table.

Thanks alot.

[r937]

speed difference is negligible