Enforcing Database Constraints In Application Code

问题:

I'm working on my first real asp.net web application and I'm kind of stumped on the best place and method to trap and handle database constraint violations. Suppose I have a unique constraint on a column and a user inputs something that would violate that unique constraint. Do I catch it in the business layer by making a call to the database to check of the value exists for that column or do I let it go all the way to the database and let it throw an exception and handle that in my application?

I don't like the latter answer- because it makes sense to handle as much as you can in the business layer before it reaches the database. But it's also somewhat problematic as my application is driven by procedures that implement basic CRUD operations on all tables. So in order for me to enforce uniqueness in the business layer, I need to create a procedure for every possible constraint so I can do a look up before an update or insert. That just seems tedious... and I'm duplicating business rules as they exist in both the database and the business layer, so if something changes, not only do I have to change the database, but I have to change the procedure and the application code.

So is there a right way or a good way to enforce these database constraints in application code or should I be looking at a way to catch exceptions thrown by the database and present them in a user-friendly way?

回答1:

The latter answer is correct. What would be the purpose of using database constraints if you try and do everything first in your front end application?

The entire point of constraints is to have a central location (the DB server) that handles as much of the data validation as possible, in a consistent fashion, regardless of how/where the data gets there. Writing the same logic all over again in your front end application is redundant, and needlessly complex. What if something changes in the constraint in the DB (something your app thinks should be valid no longer is)? You get an exception anyway.

Let the DB handle validating constraints, and handle the exceptions in your code. This allows all means you use to access and update the data to be consistent, since the DB handles it. If something changes, you update the DB with the new constraints and your apps are automatically in synch.

回答2:

I agree that it is the latter given your description of how you are using the database. It is really important to realize that even if you made a call from your business layer to validate every single possible constrain violation before doing an update/insert, a split second later the database state could change because of requests of other users and your update/insert could still fail.