enforcing database constraints in application logic

Posted on 2006-04-26
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
    LVL 86

    Assisted Solution

    >>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

    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


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
    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…
    This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
    This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now