When I review database models for RDBMS, I'm usually surprised to find little to no constraints (aside PK/FK). For instance, percentage is often stored in a column of type
tinyint would be more appropriate) and there is no
CHECK constraint to restrict the value to 0..100 range. Similarly on SE.SE, answers suggesting check constraints often receive comments suggesting that the database is the wrong place for constraints.
When I ask about the decision not to implement constraints, team members respond:
Either that they don't even know that such features exist in their favorite database. It is understandable from programmers using ORMs only, but much less from DBAs who claim to have 5+ years experience with a given RDBMS.
Or that they enforce such constraints at application level, and duplicating those rules in the database is not a good idea, violating SSOT.
More recently, I see more and more projects where even foreign keys aren't used. Similarly, I've seen a few comments here on SE.SE which show that the users don't care much about referential integrity, letting the application handle it.
When asking teams about the choice not to use FKs, they tell that:
It's PITA, for instance when one has to remove an element which is referenced in other tables.
NoSQL rocks, and there are no foreign keys there. Therefore, we don't need them in RDBMS.
It's not a big deal in terms of performance (the context is usually small intranet web applications working on small data sets, so indeed, even indexes wouldn't matter too much; nobody would care if a performance of a given query passes from 1.5 s. to 20 ms.)
When I look at the application itself, I systematically notice two patterns:
The application properly sanitizes data and checks it before sending it to the database. For instance, there is no way to store a value
102as a percentage through the application.
The application assumes that all the data which comes from the database is perfectly valid. That is, if
102comes as a percentage, either something, somewhere will crash, or it will simply be displayed as is to the user, leading to weird situations.
While more than 99% of the queries are done by a single application, over time, scripts start to appear—either scripts ran by hand when needed, or cron jobs. Some data operations are also performed by hand on the database itself. Both scripts and manual SQL queries have a high risk of introducing invalid values.
And here comes my question:
What are the reasons to model relational databases without check constraints and eventually even without foreign keys?
For what it's worth, this question and the answers I received (especially the interesting discussion with Thomas Kilian) led me to write an article with my conclusions on the subject of database constraints.