[ACCEPTED]-Mysql: Allow Null meaning-null

Accepted answer
Score: 33

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

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

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

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

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

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

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

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

Hope 1 that helps.

Score: 4

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

Score: 4

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

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

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

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

More Related questions