Say I have a users table with a name column. Then I alter the table and split the name column into two new columns: first name and last name.
How is it possible to infer this change, just from seeing the new and old columns?
1. Migration that adds firstname and lastname columns will all nulls
2. Deploy application code change to start populating firstname and lastname alongside fullname, still reading fullname in the code.
3. backfill the firstname and lastname values with a script/command/migration
4. change app code to read firstname and lastname and stop writing fullname
5. drop the fullname column
6. ensure there are no nulls in firstname and lastname
7. alter the columns to be NOT NULL
Of course, some dbs (SQLServer?) infer NULL from the empty string, or am I misremembering?
Always having the columns be NOT NULL is a fundamental cheat, after always having a PK, or is that too old school for 2025?
Yes, it just requires extra care when querying and handling the rows.
It's always just easier, if you can, to make it NOT NULL after prepopulating all rows' columns to the empty string (or real data).
Sometimes NULL is truly different than the empty string, but that, like you said, is just a kind of data.
Side note, but why do folks always bring up this "firstname lastname" example? It is not ever possible to implement that correctly in an automated fashion: some people have spaces in their first names, and some people have spaces in their last names. (I don't mean to single you out, as this exact example comes up multiple times in every declarative vs imperative discussion!)
If I can, I even avoid storing actual names of users at all. If I have to, I use a simple free form "display_name" column; what is put there is up to the user.