MySQL NULL or NOT NULL That is The Question?

Question

What is the difference between NULL and NOT NULL? And when should they be used?

Answer 1

NOT NULL means that a column cannot have the NULL value in it - instead, if nothing is specified when inserting a row for that column, it will use whatever default is specified (or if no default is specified, whatever MySQL's default is for that type).

Fields that aren't NOT NULL can potentially have their value as NULL (which essentially means a missing/unknown/unspecified value). NULL behaves differently than normal values, see here for more info.

Answer 2

NOT NULL is a column constraint and should be used when you have a column that's not in a primary key (primary keys are intrinsically not-null so it's silly to say NOT NULL explicitly about them) of which you know the values will always be known (not unknown or missing) so there's no need for nulls in that column.

NULL is a keyword occurring in many contexts -- including as a column constraint, where it means the same as the default (i.e., nulls are allowed) -- but also in many other contexts, e.g. to insert a null in a column as part of an INSERT...VALUES statement.

Answer 3

NULL means you do not have to provide a value for the field...

NOT NULL means you must provide a value for the fields.

For example, if you are building a table of registered users for a system, you might want to make sure the user-id is always populated with a value (i.e. NOT NULL), but the optional spouses name field, can be left empty (NULL)

Answer 4

What is the difference between NULL and NOT NULL?

When creating a table or adding a column to a table, you need to specify the column value optionality using either NULL or NOT NULL. NOT NULL means that the column can not have a NULL value for any record; NULL means NULL is an allowable value (even when the column has a foreign key constraint). Because NULL isn't a value, you can see why some call it optionality - because database table requires that in order to have a column, there must be an instance of the column for every record within the table.

And when should they be used?

That is determined by your business rules.
Generally you want as many columns as possible to be NOT NULL because you want to be sure data is always there.

Answer 5

I would suggest

• Use NOT NULL on every field if you can
• Use NULL if there is a sensible reason it can be null

Having fields which don't have a meaningful meaning for NULL nullable is likely to introduce bugs, when nulls enter them by accident. Using NOT NULL prevents this.

The commonest reason for NULL fields is that you have a foreign key field which is options, i.e. not always linked, for a "zero or one" relationship.

If you find you have a table with lots of columns many of which can be NULL, that starts sounding like an antipattern, consider whether vertical partitioning makes more sense in your application context :)

There is another useful use for NULL - making all the columns in an index NULL will stop an index record being created for that row, which optimises indexes; you may want to index only a very small subset of rows (e.g. for an "active" flag set on only 1% or something) - making an index which starts with a column which is usually NULL saves space and optimises that index.