enforcing database constraints in application logic

Posted on 2006-04-26
Medium Priority
Last Modified: 2010-03-31
I have a unique constraint defined  at the database level which checks for a
combination of two fields name and postcode.
I need to report an error back to the user in the web-tier.
What is the best technique for this?

e.g the logic that inserts/updates the database will throw an SQLException if this constraint is violated, should i catch this exception and throw a valid user error.

or before making the update make a call to the database to check for this constraint violation ie run a query to see if there exists 1 or more rows with the name and postcode entry and then return an error message based on the resultset returned.

I dont like the first one as sqlexception can be thrown for other reasons than unique constraint violation

any ideas?

thanks in advance
Question by:inzaghi
  • 2
LVL 86

Assisted Solution

CEHJ earned 400 total points
ID: 16545012
>>I dont like the first one as sqlexception can be thrown for other reasons than unique constraint violation

You can check the native error too
LVL 19

Accepted Solution

Jim Cakalic earned 1600 total points
ID: 16549122
As you say, SQLException can be thrown for reasons other than constraint violations. And even if you check native error codes and are able to determine that it is a constraint violation, how do you know which constraint has been violated -- there may be more than just the one over time. And even if, for a particular database vendor, the constraint name is in the message, now you'd have to parse the message to determine whether a particular constraint was violated so you could unambiguously report back to the user what the problem was. This kind of code tends, IMO, to be rather fragile.

Because of these concerns, I usually check the constraint explicitly in code as you suggest. It's much easier codewise to simply issue a 'select count(*)' with the desired criteria as part of a validation phase before attempting the insert. This is usually enough to deal with the problem unless you are working in a highly concurrent environment where it is likely that such collisions may occur. In that case, it might be easier to try the insert first and then, if it fails, determine if the cause of the failure was the constraint violation by executing the select. But that usually isn't necessary.

LVL 86

Expert Comment

ID: 16550733

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Suggested Courses
Course of the Month14 days, 18 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question