Does Null or Not Null Make any Difference?

[kuau]

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

[AmenKa]

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

[kuau]

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.

[AmenKa]

how interesting, thank you for the correction.

[Twey]

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.

[kuau]

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.

[Twey]

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.

[kuau]

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!

[Twey]

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.

[blm126]

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!).

-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.