Part 2: When To Use Them

You probably already know that setting a default value on non-null columns helps get rid of those pesky "Field 'xyz' doesn't have a default value" errors. Hopefully you're also aware that keeping error messages at bay is not in-itself a valid reason for supplying default values. There are many reasons for providing default column values - some good, and some, less so. Part 1 explored the ramifications of MySQL's Strict SQL Mode, as well as how to view and set it using Navicat for MySQL 15. In today's follow-up blog, we'll tackle when to use default values, and how to come up with good ones.

Why Not Just Allow Nulls?

Nullable columns don't present the same challenges as non-null ones do, so why not allow nulls in all non-key columns? In many instances, the point of applying the non-null constraint to a column is to force the application or system that populates it to supply a value. Other times, a non-null column might contain audit information, such as the user ID or a timestamp. In either case, you're looking for valid data, and not just filler.

That's an important consideration because it drives home the importance of generating useful defaults as well as front-end validation. I can still remember my first web application. It collected user details such as names, emails, and phone numbers. All of these fields were required, so clever users found all sorts of ways to circumvent entering their real information, such as entering phone numbers of 111-111-1111 and names such as "Elmer J. Fudd".

Generating a Timestamp

Now that we've gone over some reasons why automatically populating fields is worth doing any time you can do so, let's look at a common example of a generated value: an audit timestamp.

Several of the tables in the Sakila Sample Database feature a last_update column. These employ the timestamp data type; its value is set to the output of the MySQL CURRENT_TIMESTAMP function. In Navicat (Premium pictured below), you can set the default value via a drop-down list:

The Default value sets the timestamp on record creation, whereas checking the On Update Current_Timestamp box tells MySQL to update the timestamp on every UPDATE operation.

Sentinel Values

In RDBMS, a sentinel value is one that has a special meaning. For instance, a value of 999 in an age column would signify that it is unknown. I've also seen applications that employed "1900-01-01" for unknown dates. Sentinel values can be useful in cases where you want to assign a value of "unknown", whereas nulls mean "no value". Not everybody is fond of sentinel values because people and applications that work with the database have to be aware of all sentinel values in order to handle them properly.

Conclusion

While default - and by extension - sentinel values have their place in good database design and development, it's worth considering each value's purpose before assigning a value. Simply relying on default values to avoid working with nulls is probably not a good enough reason to do so.