How do I require a mysql field?

Question

I just discovered NOT NULL does not make a field required.

When creating a mysql table, how do I create a field that cannot contain null or blank (must have something in it)?

Answer 1

By default, MySQL accepts invalid values. You can set MySQL to strict mode to force valid values. This will reject a query that does not provide a value for a NOT NULL column as well as enforce integrity on all types of columns.

---

Update: MySQL 5.7 and above now have strict mode on by default. So it would not accept invalid values by default like previous versions.

---

http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sql-mode-important

http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_strict_all_tables

Edit: @Barranka and @RocketHazmat made good points in the comments. '' is not the same as null, so MySQL will allow that in a NOT NULL column. In that instance, you would have to resort to your code or a trigger.

In the code (PHP for example), this could be easy enough, running something like:

if (!strlen($value)) { 
    // Exclude value or use NULL in query
}