Ask HN: Adding database constraints for writes only?
Let's say we have a schema with a `users` table, with an `email` column. For whatever reason, our users don't _need_ to supply an email (maybe they can optionally provide a phone number instead), so the column is _nullable_.
But a few months down the line, we decide that all users must provide an email. So we want to change the schema so the database enforces that all users provide an email. But we can't. Because we already have a bunch of users who didn't provide an email! So we have lots of rows where `email` is null.
One solution here would be to turn all the nulls into empty strings, for the migration. This works for a time (though you now need to adjust your code everywhere to handle empty strings as `null`).
But then the business decides to switch back to allowing users to not provide a username. This leaves us with two options: 1. Fill the `email` column with an empty string, whenever it is not provided. 2. Fill the `email` column with `null` whenever it is not provided.
The second option is obviously pretty bad, because now we need to handle both nulls, and empty strings. So we'll use the first option.
All in all, not _so_ terrible, right?
--- But this is actually a special case where it works out okay, because an empty string is obviously an invalid email address, so we can treat the empty string as special. But what if we need to consider an empty string as a value, and can't use it as a null replacement? Or what if the column is a numeric type - 0 is not always a great number for the absence of a value.
The "problem" here is that database constraints apply to a whole table, including existing data, when we really want to impose a rule on writes, I'm not aware of a way to impose these types of constraints only on write, but not on read.
I imagine one approach would be to "archive" the existing table when adding constraints, create a new table with the new schema (where writes will go), and have all reads go through a view that `union`s all versions of the table. I have no idea how this could work with foreign keys though, or how this would affect performance.
Of course, another option is just to do this logic at the application level, but I like the idea of the database being able to enforce all the business rules (at least in theory, I've never tried that in practice).