SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    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.

  2. #2
    SitePoint Enthusiast webchick's Avatar
    Join Date
    Jun 2004
    Location
    in front of a computer
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post

    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);?>

  3. #3
    SitePoint Addict
    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

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Wizard TheRedDevil's Avatar
    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!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard TheRedDevil's Avatar
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TheRedDevil
    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!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard TheRedDevil's Avatar
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard TheRedDevil's Avatar
    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

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard TheRedDevil's Avatar
    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.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    speed difference is negligible

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •