Search code examples
mysqlsqldatabasenullnotnull

When to write NOT NULL when creating a MySQL table column

What's the difference or benefit of writing "NOT NULL" in a mysql field creation...

For example if I'm creating a table like this... 

CREATE TABLE IF NOT EXISTS game(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
description VARCHAR(200) NOT NULL,
PRIMARY KEY(id)
)

Here, id and name always has a value so their NOT NULL is just fine. I get that. But, description is an optional field so, it can be blank.

So, in this situation, should I put NOT NULL or not ?

Solution

  • NULL and a blank field are not the same thing (unless, under some circumstances, you're a brain-dead DBMS coughOraclecough).

    NULL means unknown or not applicable whereas a blank field means, well, known and blank.

    It depends entirely on how you want to handle the field itself. For example, let's consider the middle initial of your name. If you do not wish to distinguish between 'unknown' and 'does not have one', set the column as NOT NULL and just use a blank value to represent both - that will ease your queries somewhat.

    However, if you want to mail all your clients without middle names (for whatever bizarre reason) without bothering those where you don't know if they have one, you need to be able to distinguish between the two classes.