Mysql: Allow Null meaning

Question

What does "Allow Null" mean, and what does it do?

Answer 1

In order to understand what "Allow Null" means, you need to understand what NULL is, and understand that it is different to simply a zero or blank string.

NULL is a special value in SQL. It indicates data that does not exist. This is different to data that is blank.

When you create a table in MySQL, it allows you to specify whether your fields are allowed to be NULL. If you specify "Allow Null", then it will be possible to create records with NULL values in those fields.

This may be useful to you in some cases. For example, a Yes/No field may be a boolean value, but if the user didn't specify their preference, you may want to set to NULL to indicate this rather than defaulting to yes or no.

In many cases though, allowing NULL can be problematic. Fields set to NULL can produce unexpected results in queries, because NULL does not follow the same rules as other values in all cases. eg: If you query the above example field WHERE myfield != 1, you may expect to get results for all the records which are not equal to 1. However NULL fields will not be returned; you need to write a special case for them.

NULL is also used as the default value when doing JOIN queries where some of the results do not have a record to join to. The unknown fields from the joined table will all be set to NULL.

In general, if you're not sure what to do with "Allow Null", the safest option is to set it to no allow nulls.

See the Wikipedia entry for NULL in SQL for more info.

Hope that helps.

Answer 2

Allow Null means that when you change or insert the data you dont need to fill that data in. Its not compulsory.

Answer 3

In essence, if you allow a null value you're saying that the field in question can contain a legitimate value (e.g.: 2011-04-02 if it was a date) as well as "NULL" (or "no value").

As such, if you imagine a situation where a field was mandatory, it should always be denoted as NOT NULL in the schema declaration, whereas an optional field can be left as is.

For more information, check out the Working with NULL Values section of the MySQL manual, which (amongst other things) neatly summarises things thus:

Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.