Column Nullability/Optionality: NULL vs NOT NULL

Question

Is there a reason for or against setting some fields as NULL or NOT NULL in a mysql table, apart from primary/foreign key fields?

Answer 1

I don't know how you would define a required field in mySQL, care to enlighten me? I really don't know.

Anyway, even if this can be done, I can hardly think of a scenario where it would make sense. IMO, you would have to validate faulty (=incorrectly empty) data much earlier. Validation, sanitation and cutting should be done long before anything enters the database. The only time a database error should occur is when something exceptional occurs, e.g. when the database is physically not reachable.

Answer 2

That completely depends on your domain to be honest. Functionally it makes little difference to the database engine, but if you're looking to have a well defined domain it is often best to have both the database and application layer mirror the requirements you are placing on the user.

If it's moot to you whether or not the user enters their "Display Name", then by all means mark the column as nullable. On the other hand, if you are going to require a "Display Name" you should mark it non null in the database as well as enforcing the constraint in the application. By doubling the constraint, you ensure that should your front-end change, the domain is still fully qualified.

Answer 3

MySQL has a NOT NULL condition on a field, but this will not stop you from inserting "empty" data. There is no way to flag a field as "required".

As Pekka mentioned, you should be doing some sort of validation to prevent this at a higher level in your application.

Answer 4

It's not a MySQL specific thing - every database that I'm aware of allows for defining columns with a constraint that either allows a NULL value in the column, or does not allow this to happen.

Defining a column as NOT NULL means there always has to be a value present that matches the data type. NULL is a sentinel value, and its' data type transcends whatever is defined for the column.

If the column is a foreign key, the value also has to already exist in the related table before you insert the value into the current table. DEFAULT constraints are common, but not necessary, on columns defined as NOT NULL so that the columns will be populated with an appropriate value if NULL was attempted to be inserted into these columns. Getting back to foreign keys, a foreign key column can be nullable, which means the relationship is optional - the business rules allow for there to be no relationship.

When Should NULL & NOT NULL be Used?

---

Ideally, every column should be NOT NULL but it really depends on what the business rules require.