enforcing database constraints in application logic

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
inzaghiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
>>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
0
Jim CakalicSenior Developer/ArchitectCommented:
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.

Regards,
Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CEHJCommented:
:-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.