Schemas outlive the code that was written against them
Applications are replaced every few years. The data survives every replacement, carrying whatever decisions were made when nobody was paying much attention.
There is a reliable pattern in long-lived systems. The application gets rewritten: a new framework, a new language, a new team, a new front end. This happens perhaps every five to eight years, and each time it is treated as a substantial undertaking. The database is migrated across, largely unchanged, because changing it is harder than changing the code and because everything else depends on it. After two rewrites the schema is the oldest surviving artefact in the system, and it is carrying decisions made by somebody who left a decade ago.
This asymmetry should change how much care goes into each. A poor choice in application code is annoying and can be fixed by somebody willing to do the work, because the code has no state. A poor choice in a schema propagates: every application written against it inherits it, every report depends on it, every integration encodes it, and by the time somebody wants to change it there are eleven consumers nobody has a complete list of.
The decisions that turn out to matter most are unglamorous and are usually made quickly. Whether a thing is nullable. Whether an identifier means something or is arbitrary. Whether a relationship is enforced by the database or by whichever application happens to be writing. Whether a status is a constrained set of values or free text. Whether time is stored with a zone. Each takes seconds to decide and years to undo.
The most consequential of these is where the rules live. It is tempting to enforce integrity in the application, because that is where the developer is working and the checks are easy to write. It works until a second application writes to the same data, which always happens: a reporting tool, an import script, a migration, an integration, somebody fixing something by hand at two in the morning. Every one of those bypasses the application's rules, and the database accumulates rows that could not have been created through the intended path.
Those rows are the beginning of a specific and familiar decline. Once invalid data exists, new code has to tolerate it, so the checks get relaxed. Reports produce numbers that do not reconcile, and somebody adds a filter to exclude the odd cases. The exclusion becomes convention. A few years on nobody can state confidently what the data means, and the answer to any question requires knowing which rows to ignore, a knowledge held by two people.
Constraints in the database prevent this by making the invalid state impossible rather than merely discouraged. They are occasionally inconvenient, they will reject a bulk import that a developer wanted to push through, and that rejection is the mechanism working. A constraint that has never fired is not evidence that it was unnecessary; it is evidence that something was prevented quietly for years.
None of this argues for elaborate up-front design. Schemas evolve and should, and over-modelling for imagined requirements produces its own problems. The argument is narrower: the decisions that are expensive to reverse deserve more thought than the ones that are cheap, and in a database almost everything is in the first category.
A final observation about why these decisions get made badly, which is not carelessness. Schema decisions are made at the point of least knowledge, at the start of a project, by people under time pressure who do not yet understand the domain. That is unavoidable, and the practical response is not to demand better foresight but to make the reversible choices freely and to slow down specifically on the small number that are not. Knowing which category a decision falls into is most of the skill, and it is learnable in a way that predicting requirements is not.