Every single time I've seen someone say something like, "We don't need constraints; we have validations", they've had to clean up their data — when it turned out validations aren't enough.
Please do future-you a favor: use constraints.
Please do future-you a favor: use constraints.
Cleaning up bad data after the fact can actually be impossible. When it is possible, it can be annoying, tedious, and easy to fuck up.
All of this can be avoided by an astonishingly small amount of forward thinking. What columns should not be nullable? What are the maximum sizes needed for strings, numbers, and binary data? Which columns or sets of columns should only ever be inserted once? What columns point to other rows, should they be required, and what should happen if one side or the other has deletion attempted?
Constraints should cover basic data consistency guarantees. Validations are fine for business logic and business requirements, since those might change more frequently.
I used Postgres to store various sensor data (some Raspberry, ESP and other "mini" stuff). Then tried InfluxDB, mainly because Grafana supported it, while Postgres did not (it does now!).
Now there are sensor malfunctions, due to old age, no proper connection, bugs or whatever. A Temperature sensor which shows 85 °C while only being capable of 50 °C? You could capture that in the application, but this is cumbersome. Just do it in the database, constraint "temperature < 50" and you are done.
To top it off, deleting wrong data in InfluxDB is a real pain.
Out of curiosity, re: constraints in your example couldn't you easily do something like removeAboveValue(50) when retrieving your data from these faulty sensors? It seems like basic constraints, windowing, etc should be part and parcel of most time series databases?
> And of course you can get quite clever and fun here....create a function that checks if a function is a fibonacci number
The fact this is presented as a reasonable (trivial!) thing to embed in the DB screams, this is a terrible place to work.
I agree on the fib constraint, though. Fun, but nothing you'd ever really do.
> embedding business logic in SQL along with the server app and whatever presents data to third parties, is just making everything harder
Not in many cases. In the past 15-20 years, think about how many application stacks have come and gone. We've had Postgres that whole time, and plsql. Core business logic in the DB never needs to be rewritten, no matter how many different front-end rewrites you go through.
And logic in the DB is inherently capable of only scaling as large a your DB cluster (i.e. not very), and can cause your DB cluster to scale prematurely (an event which incurs a lot of potential technical debt and additional monitoring).
If "you can't have a price <= 0" is a business rule then it should be enforced in all layers: in the UI because you want to warn the user they're trying to do something incorrect without sending obviously invalid data to the server, in the back-end because you don't want to write incorrect data to the database, and in the database because you don't know that someone else will not write "just a quick app" that will bypass your back-end and mess up the data.
Of course you can. For example, constraints inside triggers. A simple insert trigger will only apply on records going forward.
Also, the reason why you bother with "additional" constraints by implementing them on the database is because the database is typically the thing that can enforce them. Unless you go out of your way to roll your own single-write via queue or something.
I'm not sure I agree with your take-away since you can just drop constraints. Easy come, easy go. Meanwhile bad data is easy come and potentially a nightmare to fix.
Or you can just expand the constraint to superset of two, or figure out how the old data needs to be treated by new system and migrate it. There are many right options if you actually care about data integrity that are not as easy as not caring. Doesn't make it good general advice not to care.
If there was a way to distinguish "old" and "new" data, you could write your constraint to honor the difference.
> therefore you must implement those constraints somewhere else and if you have those constraints there anyway, why bother with additional ones.
IMHO, if you must only have one set of constraints, put it in the data store.
The thing about constraints is that they provide pretty solid guarantees that let you better reason about the data at its base level.
You can't reason well about data that was "checked" by dozens of different versions of validations over time. What you have in that case is garbage in need of cleanup.
So you'll never again use a uniqueness or foreign key constraint?
Also, I don't know what pocmeans, bit it provides no insight into the problems you had and why it was ok to accept otherwise bad data.
> The fact this is presented as a reasonable (trivial!) thing to embed in the DB screams, this is a terrible place to work.
Yes, a cute example showing you that you can constrain by anything is indicative of how terrible a place this is! /s
A filesystem is just a hierarchical key-value database.
Assuming you do believe in file permissions, why wouldn’t you use the additional sanity of validation available in a SQL database?
Why not have two databases, one with all data without constraints and another, or even many which impose various constraints?
Bad data in the context of database constraints is data that violates your data model which, in the absence of constraints, is implicit and tucked away around the application layer in comments and functions and word of mouth. If you're cleaning up data, that means your data integrity has been compromised.
If you're collecting unstructured data from scientists, there's no contradiction. Maybe you make a nullable byte blob column for it. That's generally the most nonthreatening data in the database. Data constraints don't mean you need perfect data.
But the fact that your mental image is the weirdly specific scenario someone deleting data while "their scientists" go "aw snap, I wanted that" indicates a misunderstanding on your part about what's being discussed.
It sounds like you think that the opposite of data constraints is a windfall of valuable data that someone wants. No, exactly the opposite is what you're protecting yourself from with constraints.
If someone protects me with the constraint of one-to-one customer to address, then maybe I lose the fact that the customer has just opened a second office.
All I'm saying is that database constraints go both ways, they reveal bugs and they cause bugs.
In this case, yes, you should absolutely be dropping it on the floor (and alerting the devs with an exception) rather than creating a mess that needs cleaning up later.
Keep the data. Log that it's weird.
I ask because it would not have once occurred to me in the past fifteen years of software development that “enforcing database constraints” in any way implies shutting everything down and stopping transactions until the cause of a violation is rooted out.
If you’re approaching it from the position of collecting raw data from sensors or something, that might make sense. But from the perspective of a webapp, allowing duplicate (or empty) usernames or failing to keep foreign key relations valid often violate basic assumptions of soundness that the application is based upon. In these cases, the right answer is to drop the data on the floor (loudly) so a developer can look at it. Inconveniencing a customer once here is infinitely better than inconveniencing them constantly due to the site being buggy or broken as a result of the invalid data, and prevents needing to migrate the data in the future which can actually be impossible to do correctly in some cases.
Web dev for some years, then data science.
> implies shutting everything down
That's what "loudly" implied to me. Loud means crash, no?
> basic assumptions of soundness
I like to enforce that at the application layer, so that I can quickly swap storage without worrying. Whether I'm writing to database or a mock object, I'd hope the exceptions that arise in the application are the same. Things like unique index are easy to implement with a mock object, but more complex constraints I'd rather enforce in code that I find easier to read.
> buggy or broken
All I'm saying is not to be an absolutist. Some things are good to have as database constraints, others are good as application logic. I'm arguing against the idea that "validations are [never] enough." In contrast, database constraints are almost never enough to manage the complexity of data integrity.
Logs sent high priority to dev team for fixing.
You stop the transaction on a constraint violation, not usually the entire system.
> Keep the data. Log that it's weird.
I've worked on systems that do that, and they're nightmares.
The problem with that style can be summed up in "if it doesn't look broke, people don't bother fixing it." No one actually watches those logs, and users don't try to fix data that was "accepted" with no errors. The result is bad data piles up, and often causes problems in other parts of the system.
Maybe you (as a developer) love working on support tickets to manually fix bad data the users entered or a buggy process created, but I sure as hell don't.
The solution to this is to fail fast: https://en.wikipedia.org/wiki/Fail-fast.
The idea that troubles me is including more strict constraints in the database than in the application itself, because that indicates the application isn't fully tested. If the application is well tested, then it doesn't matter where the logic resides, db or app, so there's no standing to the claim that application logic is never enough.
Also, there are many kinds of data. Transactions, sure, reject if it doesn't match. Telemetry data, I'd like to hang on to it regardless of how weird it seems.
A constraint can allow "bad" data if the "bad" data is still semantically valid for that column.
A freeform text column where the scientist writes notes might not need any constraints. But a column used to send measurements to a robot to manufacture something, must have some protections around what type of data it can store. And no, the scientist doesn't get to input "bad" data that makes the robot lose its mind.
Sometimes yes, you drop data on the floor.
Do you think it's superior to be in a situation where you thought all of your IDs were numbers, you built your system with that expectation, and it turns out strings have been silently making their way into your data?
The difference between the two scenarios is that the person that used constraints knows when and why and where they got unexpected data and they can take more decisive, informed action. Meanwhile the other guy is desperately writing queries to find unexpected data, shooting around in the dark, and wondering what else slipped through.
It’s a miracle our stuff works as well as it does. A more patient man could probably create a good deal of programming literature trying to unpack that paradox. Instead I’m hunting for something where people are a little less concerned about job security and a little more open to mentoring, robustness, and low drama development processes.
No, I’m not open to that kind of education. I’ve had plenty of volunteers to teach me if I ever change my mind. In the meantime I’ll try to work in the half (quarter?) of the industry that strives for something better.
As someone once told me, I don’t need to be a good fit for every job. I just need to be a good fit for one at a time.
Life is short. Find better mentors instead of trying to eek out a little wisdom from bad ones.
A good scientist tries to find evidence that contradicts her theory. Evidence that is consistent with the theory proves nothing.
It's a constant fight between analyst and application developer. The analyst wants a history of all states. The application developer often only cares about having a correct current state.
Again, this is totally and completely wrong. Actually, it doesn't even make sense, to say it's wrong. If I'm building an application to be used by an analyst, I want to hit all their requirements. They tell me what sort of data they want to be collected. If requirements change, or the data isn't as valuable as originally thought, or it's badly formatted, or whatever, then we work together to change it.
I don't fight with anyone.... They don't want shit data, and I don't want to store shit data. So who is there to fight with?
Anyway, we're going way down a rabbit hole. All I wanted to say is that if the user, sensor, or application logic emits something weird, sometimes it's correct.
I’ve run a number of event sites for luxury car brands and it was stunning how many double-click form submissions we received on those. Often just milliseconds apart.
Laughable.