7

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?

Community
  • 1
  • 1
C.J.
  • 3,409
  • 8
  • 34
  • 51
  • 1
    Both type of validation/constraints have their needs separately. The example you take, is what we can handle at application level using max length for that field. But Their are still needs constraints such as Foreign key constraints, Unquie Key Constraints, Primary Key, and many more. So we can't eliminate the Database level Constraints. We need both, depends on the scenarios & project needs – HaveNoDisplayName Nov 10 '14 at 19:26
  • My point is - you can never predict how the data will change in the future, open up the flexibilities by not having any constraint at the database level will ease up any future maintenance. And yes, I have encountered cases where `Primary Key` was changed to an entirely different column – C.J. Nov 10 '14 at 19:31
  • "you can never predict how the data will change in the future, open up the flexibilities by not having any constraint at the database level will ease up any future maintenance. " However we can predict with *absolute certainty* that a databases without any constraint will end up riddled with duplicate data, orphaned records and non-conformed values. – APC Nov 15 '14 at 14:17
  • 1
    Despite DBAs love to ensure validation on database level, it's absolutely impractical: you have to validate data **in JavaScript** and often, on client-side, to be Web 2.0+. Even if you validate everything by creating constraints, how you going to show the user your validation errors? As answers from the underlying DB engine? LOL not forget to localize them when you'll be translating your site into Chinese. – Brian Cannard Sep 20 '16 at 19:20
  • @BrianHaak I like your attitude, but I think OP meant where to put the enforcement, not validation, and as being said here it is important to enforce the entities relations on the DB level (you're gonna need to validate in client side anyway) – refaelio Oct 29 '18 at 10:36
  • It's a discussion like "Mongo vs. Postgres," and I'm on the latter side. I think you're trying to mix the concepts of validation and constraining. Validation should support API and interaction with a "user." And my whole point was about it. Constraining is suported in DB schema, and implementing it in the server code is silly. Both things are required. Constraining in DB, and validation in server-side application code, preferably a JavaScript Node.js application, so the same code can be used both on server and client sides, and beautiful localization support. – Brian Cannard Oct 30 '18 at 04:54

3 Answers3

7

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
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.

Sean
  • 2,990
  • 1
  • 21
  • 31
1

Database systems provide a number of benefits, one of the most important is (physical) data independence. Data independence can be defined as an immunity of application program to change in the way that the data is physically stored and accessed, this concept is tightly related to data-model design and normalization roles where data constraints are fundamental.

Database sharing is one of the application integration patterns, widely used between independent applications. Tradeoff will be trying to spread data integrity code in all applications or in a centric fashion inside database.

Minimizing the database constraint will minimize usage of wide range of well-known, proven technologies developed over many years by a wide variety of very smart people.

As a foot note:

This table has many dependencies and will also affect many other tables not only in the database but also a few other systems

Beside this smells redundancy, at least it shows the side effect of the change. Think about when you have to find the side effects with code review!
Application comes, applications go but data remains.

Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46