Database constraints vs Application level validation [closed]

Question

While researching the topic, I came across this post: Should you enforce constraints at the database level as well as the application level?

The person who answered the question claimed that we should enforce Database constraint because it is "easier, integrity, flexible".

The reason I brought out this question is because of my recent maintenance work in one of a very robust systems. Due to a change in business rule, one of the data columns used to have CHAR(5) is now accepting 8 Characters. This table has many dependencies and will also affect many other tables not only in the database but also a few other systems, thus increasing the size to CHAR(8) is literally impossible.

So my question goes back to the database design - wouldn't it be so much easier if you reduce or even eliminate the need of database constraints? If the above mentioned scenario would have happened, all you have to do is to change the front-end or application level validation to make sure the user enter 8 characters for that field.

In my opinion, we should minimize the database constraint to anticipate any changes in the data structure in the future. What is your thought?

Answer 1

It's easier to maintain 100 tables than 100,000 lines of code. In general, constraints that are enforced in the application but not in the database have to be replicated across many applications. Sometimes those applications are even written and maintained by different teams.

Keeping all those changes in sync when the requirements change is a nightmare. The ripple effect is even worse than the cases you outline for changing a five character field into an 8 character field. This is how things were done before databases were invented.

Having said that, there are situations where it's better to enforce the constraints in applications than in the database. There are even cases where it's better to enforce a constraint in both places. (Example: non null constraint).

And very large organizations sometimes maintain a data dictionary, where every data item is cataloged, defined, and described in terms of features, including constraints. In this kind of environment, databases actually acquire their data definitions from the dictionary. And application programs do the same thing, generally at precompile time.

Future proofing such an arrangement is still a challenge.

Answer 2

I agree with you that, constraints like the length of the field should be avoided, you never know how your business will changed. and hardware nowadays are cheep, it really not necessary to use CHAR(8) just for less storage.

But those contraints like not null constraints,duplicate check and foreignkey constraints for a header details table is better to be kept. it's like the goal keeper of your data intergrate.