Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Does Null or Not Null Make any Difference?

  1. #1
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    647
    Thanks
    287
    Thanked 15 Times in 15 Posts

    Default Does Null or Not Null Make any Difference?

    Does anyone know if changing a field definition in a MySQL table from NULL to Not NULL will have any repercussions on php code that references the table? Nulls don't work well when I import the tables to MS Access.

    Thanks! e

  2. #2
    Join Date
    Oct 2007
    Posts
    53
    Thanks
    13
    Thanked 1 Time in 1 Post

    Default

    the way i understand it, null would mean 0, nothing, etc. not null would mean 1 or greater... so changing it from null to not null should affect the table... (if you dont get a syntax error trying to change it)

    maybe i am completely wrong, but i hope that helped

  3. The Following User Says Thank You to AmenKa For This Useful Post:

    kuau (07-18-2008)

  4. #3
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    647
    Thanks
    287
    Thanked 15 Times in 15 Posts

    Default

    Dear AmenKa: Thanks for your input. Actually I did change all the filelds to Not Null and nothing happened. You have a slight misconception of Null though. Both Null and Not Null mean the field is empty (not greater than 0) but in some cases Null means that the field has never had anything in it. God knows what difference that makes. I don't really understand the finer points of the definition. Maybe someone here can explain it to both of us.

    Thanks for contributing.

  5. The Following User Says Thank You to kuau For This Useful Post:

    AmenKa (07-18-2008)

  6. #4
    Join Date
    Oct 2007
    Posts
    53
    Thanks
    13
    Thanked 1 Time in 1 Post

    Default

    how interesting, thank you for the correction.

  7. #5
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    As well as all possible values of a column type, including values such as zero or an empty string (sometimes confusingly referred to as a 'null string' for C reasons) there is also an additional value that can be assigned to any column by default, NULL, which represents 'no value', and is inserted if you provide no value for the field unless you declare a different default. Declaring a column NOT NULL results in the NULL value being disallowed: it is no longer valid and will throw an error to attempt to update a field to NULL, or fail to specify a value for a column without a non-NULL explicit default. A NOT NULL field takes slightly less space to store than its nullable equivalent.

    This modification will have no effect on the workings of your PHP scripts, unless they rely on NULL values, in which case things will break.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  8. The Following User Says Thank You to Twey For This Useful Post:

    kuau (07-18-2008)

  9. #6
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    647
    Thanks
    287
    Thanked 15 Times in 15 Posts

    Default

    Thanks, Twey. From your explanation I infer that it is just fine for me to use Not Null all of the time because I prefer it. I cannot imagine an example of setting it to Null being useful. Do you know any? Thanks.

  10. #7
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Yes, when you wish to have a value that represents a lack of a value: for example, when you need to distinguish between an empty string or zero and an actual lack of a value. In relationships, as well, NULL is necessary to prevent the database failing to enforce a relation, and it can be used with the two standard boolean values to implement trinary logic in database systems that support a boolean type.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  11. #8
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    647
    Thanks
    287
    Thanked 15 Times in 15 Posts

    Default

    Say what? You lost me on that one. That's exactly what I can't think of an example of, a situation "when you need to distinguish between an empty string or zero and an actual lack of a value." When exactly in this lifetime would such a situation arise?

    The next sentence leaves me staring blankly at my screen wondering if I speak this language. Vielleicht nicht.

    Danke!

  12. #9
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    For example, an optional integer field. No integer value can be used to indicate a lack of a value, because that integer could potentially be a value in itself, including zero. A value is required that is outside the set of valid values for that type. That is NULL.

    With regards to the second sentence, while I would be delighted to write an extraordinarily long post giving you a grounding in basic database theory, I suspect a tutorial would be a better use of our time.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  13. The Following User Says Thank You to Twey For This Useful Post:

    kuau (07-18-2008)

  14. #10
    Join Date
    Sep 2005
    Posts
    882
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Default

    Ok, I guess I'll take a shot at explaining NULL. Basically, as Twey said, NULL is normally used to indicate the lack of a value. For example, think of a database that contains threaded comments. Comments could be in reply to another comment, so the database tracks this relationship through the use of an integer that is equal to the primary key(a unique integer, in this case) of it's parent comment. Maybe a diagram would help(that sounded confusing to me, and I wrote it!).
    Code:
    -Top Comment, id:1 parent_id:NULL
       -Sub Comment id:2 parent_id:1
       -Another Sub comment id:3 parent_id:1
            -Sub Sub Comment id:4 parent_id:3
    -Another Top Comment id:5 parent_id:NULL
    NULL is useful in this case because it provides a value that doesn't belong to the set of integers. It is the value of parent_id that would indicate a comment not in reply to anything.

    NULL in general is one of the trickiest parts of SQL. If you don't absolutely need it, avoid it. So in your case, if your PHP code always sets a valid value for every column in every INSERT and doesn't explicitly set a column to NULL in an UPDATE, then NOT NULL will not break everything(and will probably make the code easier to maintain for someone else). However, if it does do either of those things, then NOT NULL could be break your PHP(or really, your SQL) code real fast.

  15. The Following User Says Thank You to blm126 For This Useful Post:

    kuau (07-18-2008)

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
  •