Select Page

Why I Avoid DEFAULT Column Values In My SQL Database

Ben Nadel
Published: May 2, 2024

When defining a relational database table schema, each column can have a type and, optionally, a default value to use if no value is provided in a subsequent row INSERT statement. When evolving a table schema, these DEFAULT value mechanics make migrations tractable. But, as soon as I can, I drop the DEFAULT setting from the column definition. In fact, I try to limit my use of DEFAULT values as much as possible.

As discussed in an earlier post on using an existing column to define a DEFAULT value, when I migrate my database schema, I use the following workflow:

  1. Add a new database column with a sane DEFAULT value.

  2. Update my ColdFusion code to make sure that an explicit value is always provided with each INSERT statement.

  3. Drop the DEFAULT value from the recently-created database column.

If I perform steps 1 and 2 and skip step 3, my application will continue to work. But, I make sure to always perform step 3 because I want my ColdFusion application to be the source or truth. This is the same reason that I don’t use stored procedures. The application code should reveal everything that I need to know about how the data is used. And, more importantly, what shape the data needs to be.

When the application code doesn’t have to be explicit about the data being persisted, it leaves the code open to interpretation: was the given logic omitted on purpose? Or, was the given logic omitted in error? This lack of clarity is a powerful “code smell”. It is an indication that something is wrong and needs to be fixed.

Conversely, from the database administration perspective, everything in the database schema should exist for a reason. If a future developer or DBA (database administrator) looks at the schema and sees a DEFAULT value, they have to assume that the DEFAULT setting is there because the application requires it. And that dropping said DEFAULT value would break the application.

If having a default value is helpful to the application, the default value should still be defined explicitly in the application code. Meaning, the INSERT statement must include every column value; and, any optional column values should be defined using fallbacks within the application logic and / or data-access function signatures. This way, the application code remains the source of truth while still providing some level of flexibility.

Aside: In MySQL, dropping a DEFAULT value can be performed as an online DDL operation. Which means that it doesn’t lock the table or degrade the user experience.

At the end of the day, this is a matter of “clean code”. Which is, itself, a matter of great subjectivity. For me, having DEFAULT values in the database feels messy—like a half-formed thought in need of completion. The more I can remove DEFAULT values, the more easily I can understand the true intent of the application.


https://bennadel.com/4648

Source: www.bennadel.com