Re: Check constraints, the impossible dream?

Please ignore the more abrasive comments from Daniel Morgan, he used to think he was an Oracle expert, and now that he's read Tom Kyte's book he "knows" that he is.

Tom Kyte, however, demonstrates the need for patience, explanation and proof - which Daniel Morgan seems to have overlooked.

The problem you are commenting on is a generic design issue.

    If you want to make the database run as efficiently     as possible, the optimum strategy is to be a real     bastard to the end-users. You do this by putting     all validation in the database, as close to the data     as possible, and don't try to interpret errors that     the database produces.

    If you want to keep the end-users well-informed,     you either code the validation in the front-end,     or you validate each field by calling the database     each time the user modifies a field on screen. The     former option eventually results in the front-end     and the database diverging in their validation test,     the latter is a massive performance hit.

ONEPOSSIBLE answer to your question about 'always raising the same exception' is that although is it the same exception, the default message (which you can find whilst running the WHEN clause) will be available through a call to sqlerrm. The name of the exception will always be inserted in the same place in this text, so you can extract it and determine the error to forward based on the name you find.

There are several variations on solving the generic problem, though. Your choice tends to depend on how much hardware you have, how complex your system is, and how complex you want your code to become.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


damorgan wrote in message <3C9A72A6.F0F62447_at_exesolutions.com>...


>I think someone there may need a serious lesson in the Oracle security

model.


>Your statement makes no sense.

>

>Daniel Morgan

>

>

>

>John Ashton wrote:

>

>> Yup, I can see that store procs are good. I guess I'd also have to make

sure


>> noone except a DBA had access to SQL (for updating/inserting). Hopefully

a


>> DBA using SQL*PLUS can figure out the constraint message.

>>

>> But what about the other problem with more than one check constraint per

>> table?