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

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 2

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.