MySQL column defaults - advantages/disadvantages and should I use for all columns?

Question

1) What are the advantages and disadvantages of supplying a default value for columns in a table?

2) Furthermore, should all columns have default values? I mean, seems like a good idea... why not? :)

Many thanks

Answer 1

1) What are the advantages and disadvantages of supplying a default value for columns in a table?

Advantages are that you can set the default, rather than using the standard default. This also simplifies insert statements in that you don't need to specify so many values.

No real disadvantages as long as you have reasonable defaults.

2) Furthermore, should all columns have default values? I mean, seems like a good idea... why not? :)

This really depends on your business logic. For instance if there is a column like CUSTOMER_NAME which you could not possibly come up with a good default value, then you should not specify a default. But for other columns like ACTIVE or DELETED that could be true or false, you should probably specify a default.

I hope this answers your questions.

Answer 2

All columns will have a default value, whether you specify it or not. If you don't specify, it will simply use the default default value for that type.

For a column that allows nulls, the default value will be NULL. For a default value that doesn't allow NULLs, an INTEGER column, for example, will have a default value of 0.

You should specify a default value when you want something other than the default default value.

Default values get applied only when you INSERT a new record.

A good use case for a default value is to enforce the least privilege paradigm in security.

If you have boolean (TINYINT) fields in your ACL table such as "allowUpdates" andd "allowDeletes", then you should default these to FALSE (0), so that when a new user is created, they will get the least privileges until you specifically set them.

If you follow the least privilege paradigm, then an attacker cannot trick the system into giving him high privileges simply by tricking the system into creating a new user account.

So, the disadvantages could be serious if you don't choose proper defaults. The advantages are that you can override the defaults to a sane value for your application.

Other advantages include that when inserting a record, if you want the default value for a particular field, you can simply not set it. This can make your query shorter and simpler. Also, it lets the configuration be handled in the database. In other words, you can change the defaults (in the database) without changing the application.