MySQL 8.0.13 Can Use An Existing Column As The DEFAULT Value For A New Column

In my Dig Deep Fitness app, I need to start differentiating between the date on which a record was created in the system and the date that said record represents for the user. For example, when back-filling workout data, an "exercise performance" from "3 years ago" might be entered into the app "today". As such, I need to add an executedAt date/time column; and, for all existing rows, I need to default said column value to match the existing createdAt column. As of MySQL 8.0.13, it turns out that you can do this in the actual table column definition.

Given a table resistance_exercise_instance, with an existing column, createdAt, I can add a new column, executedAt, using the following ALTER statement:

ALTER TABLE
	`resistance_exercise_instance`
ADD COLUMN
	`executedAt` datetime NOT NULL DEFAULT ( `createdAt` )
;

Here, the construct DEFAULT( `createdAt` ) tells MySQL to populate the new executedAt column with the corresponding createdAt value for all existing rows. Easy peasy, lemon squeezey!

Of course, I'm somewhat anal about how my MySQL database columns are defined. Personally, I don't like having default values because it's too much "magic"; and, I'd rather have all default values handled explicitly in the application code where they can be seen and understood by the developers. As such, my actual workflow for using an expression like this would be to:

1. Add new executedAt column with DEFAULT( `createdAt` ) in order to get the foundations in place.

2. Update my ColdFusion application code to start providing the executedAt column value during INSERT operations.

3. Run a subsequent ALTER TABLE statement for executedAt that removes the DEFAULT expression.

This way, my ColdFusion application (is forced to) become the "source of truth" for all data.

Prior to MySQL 8, in order to add this type of column, I would have had to add the column with a default value of NULL (mega barf!); and then, run a subsequent SQL script to populate the column with the desired value. Being able to set a default using an existing column makes this quite a bit easier!

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/4493

Reader Comments

Ben Nadel Jul 29, 2023 at 2:26 PM

15,902 Comments

As a quick follow-up to this post, I also just learned that you can drop a default setting on a column without redefining the whole column:

ALTER TABLE
	`resistance_exercise_instance`
ALTER COLUMN
	`executedAt` DROP DEFAULT
;

Of course, I can only do this after I've updated my application code to handle the explicit value in the INSERT statement. But, I like this - keeps things controlled by the app.

Post A Comment — ❤️ I'd Love To Hear From You! ❤️

Post a Comment →