Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Should I check for DB constraints in code or should I catch exceptions thrown by DB

I have an application that saves data into a table called Jobs. The Jobs table has a column called Name which has a UNIQUE constraint. The Name column is not PRIMARY KEY. I wonder if I should check for duplicate entries myself before I try to save/update a new entry or if it's better to wait for an exception thrown by the data access layer. I'm using NHibernate for this App if it's of any importance


Thanks to everybody for the great input.

I have found one more reason why I should validate in code and not just wait for an exception being thrown (and caught by my code). It seems that NHibernate will only throw an NHibernate.Exceptions.GenericADOException which is not very informative regarding the cause of the exception in this case. Or am I missing an aspect of NHibernate here?

like image 574
trendl Avatar asked Jan 01 '09 19:01

trendl


People also ask

How do you handle database exceptions?

You should always write exception handlers to handle exceptional conditions. The most pragmatic approach to catching database exceptions is to test your application with exception scenarios. Determine the likely exceptions that might occur for an operation in your code, and force the exception to occur.

What is DB exception?

The DbException class is an abstract class used as the base class for provider-specific exception class implementations. For general information about handling exceptions for a . NET Framework data provider, see SqlException.

What is the name of the exception related to database in C++?

Delphi Exception Handling in C++ C++Builder libraries may throw exceptions when something unexpected occurs.


2 Answers

The answer is: both.

If your database has constraints it can guarantee certain invariants about the data, such as uniqueness. This helps in several ways:

  • If you have a bug in your application, violating the constraint will flag something that might otherwise not be noticed.

  • Other users of the database can assume more about the behaviour of the data as the DBMS enforces invariants.

  • The database protects itself from incorrect updates that violate the constraints. If you find you have some other system or interface populating the database down the track, the constraints enforced by the database mean that anything caught by the constraints won't (or at least is less likely to) break your system.

Applications and databases live in a M:M relationship in any but the most trivial cases. The application should still have the appropriate data and business rule validations but you should still not plan for your application being the only customer of the data. Work in data warehousing for a few years and you'll see the effects of applications designed by people with this mindset.

like image 118
ConcernedOfTunbridgeWells Avatar answered Sep 30 '22 00:09

ConcernedOfTunbridgeWells


If your design is good (both database and BL), the database shouldn't have any constraints that wouldn't be dealt with in the BL - i.e. you shouldn't be presenting the database with inconsistent data. But nothing is perfect.

I've found that confining the database to data consistency constraints lets me handle all BL validation in procedural code, and the only cases where I experience database exceptions are design and coding errors which can (and should be) fixed.

In your case, checking the name for uniqueness is data content validation, properly handled in code. Which presumably catches the error nearest the point of commission, where you hopefully have friendlier UI resources to call on without introducing undesirable coupling between abstractions.

like image 35
dkretz Avatar answered Sep 29 '22 22:09

dkretz