A column that is nullable but never null might indicate that it should be non-nullable but does not necessarily imply so. Say you have an optional comment field, it might just happen by accident that a comment was recorded for each row, but that of course becomes increasingly unlikely with each additional row in the table. There is probably no harm in checking your database for such columns, especially in tables with many rows, but in the end it is up to the semantics of the data model whether a column should be nullable or not. The absence of NULLs is an indicator but not a determiner.
Nullable but not null
11–20 of 49 posts
Re: Nullable but not null
#12> But a field that is nullable in the schema and never null in practice is a silent lie. This seems to be the central claim. But just as lies require intent, so does database design to some degree. A column that is nullable but never null does not conclusively say anything, really. That's like saying a birthday column that is never before 1970 in the current data should be restricted to years after that date. A nulla…
I don't think the author is talking generally about fields that could be NULL but just happen to never be so in the production DB. The piece is specifically in the context of a new database field that is fully intended by its designer to be NOT NULL, which was NULL only for migration purposes, and which was never updated to be NOT NULL once the migration is complete. The point was not meant to be extended beyond that…
In that case we read it entirely differently — the last paragraph explicitly mentions inferring "Nullable fields that never hold nulls are often the result of incomplete migrations or forgotten cleanup steps". The author also proposes a script "to identify cases where the field could safely be changed to non-nullable". But alas, we cannot do that with full generality, due to there being a big difference in intent that cannot be captured purely by counting nulls, and surely not by only calculating percentages.
Re: Nullable but not null
#13> But a field that is nullable in the schema and never null in practice is a silent lie. This seems to be the central claim. But just as lies require intent, so does database design to some degree. A column that is nullable but never null does not conclusively say anything, really. That's like saying a birthday column that is never before 1970 in the current data should be restricted to years after that date. A nulla…
Problem is you end up other places with the assumption thar it's never null. So in the future when you actually set it to null somewhere it will blow up.
People that assume that a (nullable) value is never null because the sample at hand does not contain a null value _will_ learn their lesson the hard way, the one you describe.
But even non-nullable does not always resist to time, I'd argue that use cases where the field _has_ to be null eventually emerges and somehow have to be mitigated. There is no easy solution to safely workaround that without either tons work that duplicates lots of things or taking risks by adapting the code base.
Re: Nullable but not null
#14I completely agree. Unfortunately, this is one of those things that’s hard to convince people of. You often hear: ‘It doesn’t matter,’ ‘It works fine without it,’ or ‘Why overcomplicate things?’—but that mindset can be limiting.
Re: Nullable but not null
#15A column that is nullable but never null might indicate that it should be non-nullable but does not necessarily imply so. Say you have an optional comment field, it might just happen by accident that a comment was recorded for each row, but that of course becomes increasingly unlikely with each additional row in the table. There is probably no harm in checking your database for such columns, especially in tables with…
there is also other scenario, field might obviously looks like it should have value, and in check it might event always have it, but it might be "lazy" value.
eg. you might have some bug CSV uploaded and your have number of rows in it, your app could insert record without this number and async process would fill that later.
there might be even some corner case where null value is possible
I believe solution here isn't to check what fields do not use null, but to improve process of creating such migration. either you should create second ticket for next release to update db or commit new migration to some kind of next branch.
Re: Nullable but not null
#16I've seen worse. Some teams use JSON for their data. Not only each field can be missing (aka NULL), it can also be "null". Or a different type.
I envy your team who's only mistake is to forget setting NULLABLE. Rainbows and unicorns ;)
Re: Nullable but not null
#17A column that is nullable but never null might indicate that it should be non-nullable but does not necessarily imply so. Say you have an optional comment field, it might just happen by accident that a comment was recorded for each row, but that of course becomes increasingly unlikely with each additional row in the table. There is probably no harm in checking your database for such columns, especially in tables with…
> There is probably no harm in checking your database for such columns
The harm is the same as any other unreliable linter rule.
Each one such rule is almost harmless. And on most places that use that kind of rule, they are extremely harmful.
Re: Nullable but not null
#18I've seen worse. Some teams use JSON for their data. Not only each field can be missing (aka NULL), it can also be "null". Or a different type. I envy your team who's only mistake is to forget setting NULLABLE. Rainbows and unicorns ;)
That's just the JSON equivalent of "we have data, and it's null" vs "data is missing", and consistency could be enforced using a constraint or by making it non-NULL in the first place.
It's more common in string fields, which in many cases just get rendered on a web form that doesn't differentiate blank (empty string) from null state, therefore in the database we should in most cases set it up as follows:
- Value required: field should be non-NULL + at least length 1 (via check constraint)
- Value optional: either field is non-NULL, or field is nullable + at least length 1
I'm curious if you prefer to store optional strings as non-NULL and rely on the length, or as nullable and have a length constraint.
Re: Nullable but not null
#19How would the database know whether the other app layers depend on that value or not? You could absolutely have an app that does not require data in a specific field to function, yet all records happen to have data. This is actually fairly common in single-tenant apps, where some tenants populate a field and others do not. You need to look at how the data is used across the entire stack to know whether or not it shou…
It doesn't. That's why it's the responsibility of the application layer to correctly implement the data model of the database and not the other way around
Re: Nullable but not null
#20I've seen worse. Some teams use JSON for their data. Not only each field can be missing (aka NULL), it can also be "null". Or a different type. I envy your team who's only mistake is to forget setting NULLABLE. Rainbows and unicorns ;)
What if almost everything is NULLABLE? including the supposedly primary key and foreign keys of the table?
I've had the firsthand experience building a consumer for that kind of DB and it's hell to get anything running correctly without literally writing a dedicated layer to sanity check everything.