SitePoint Sponsor |
|
User Tag List
Results 1 to 14 of 14
-
Feb 6, 2005, 08:14 #1
- Join Date
- Feb 2003
- Location
- eez
- Posts
- 331
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
When to make mysql fields NULL and NOT NULL... I'm completely lost.
Hi!
When to make mysql fields NULL and NOT NULL... I'm completely lost.
Thank you for your help.
-
Feb 6, 2005, 08:30 #2
- Join Date
- Jun 2004
- Location
- in front of a computer
- Posts
- 94
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.<?$a='496620796f752063616e2072656164207468697320796f7527726520'.
'616c6d6f7374206173206269672061206765656b206173204920616d203b29'
;$b=explode("\n",chunk_split($a,2));$c='';for($i=0;$i<count($b)
-1;$i++){$c.=chr(base_convert($b[$i],16,10));}echo nl2br($c);?>
-
Feb 6, 2005, 09:07 #3
- Join Date
- Feb 2003
- Location
- eez
- Posts
- 331
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Feb 6, 2005, 09:25 #4
-
Feb 6, 2005, 09:53 #5
- Join Date
- Sep 2004
- Location
- Norway
- Posts
- 1,198
- Mentioned
- 4 Post(s)
- Tagged
- 1 Thread(s)
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!
-
Feb 6, 2005, 18:11 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Feb 7, 2005, 05:11 #7
- Join Date
- Sep 2004
- Location
- Norway
- Posts
- 1,198
- Mentioned
- 4 Post(s)
- Tagged
- 1 Thread(s)
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.
-
Feb 7, 2005, 05:50 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by TheRedDevil
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!!
-
Feb 9, 2005, 19:15 #9
- Join Date
- Sep 2004
- Location
- Norway
- Posts
- 1,198
- Mentioned
- 4 Post(s)
- Tagged
- 1 Thread(s)
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
-
Feb 9, 2005, 19:44 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
sorry, i did not quite understand that last example
could you please illustrate it with a few sample rows?
-
Feb 10, 2005, 07:55 #11
- Join Date
- Sep 2004
- Location
- Norway
- Posts
- 1,198
- Mentioned
- 4 Post(s)
- Tagged
- 1 Thread(s)
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
-
Feb 10, 2005, 08:06 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Feb 12, 2005, 20:01 #13
- Join Date
- Sep 2004
- Location
- Norway
- Posts
- 1,198
- Mentioned
- 4 Post(s)
- Tagged
- 1 Thread(s)
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.
-
Feb 12, 2005, 20:59 #14
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
speed difference is negligible
Bookmarks