Re: Check constraints, the impossible dream?

John Ashton wrote:
>
> I've always thought that using declarative constraints the preferred
> method of validating fields in an Oracle database. However, I'm now
> having serious doubts about the usefulness of check constraints.
>
> The 1st problem is that a check constraint such as "ADD CONSTRAINT
> CK_TABLE_FIELD CHECK (value > 0)" returns the extremely terse and
> unhelpful message upon a failure: "ORA-02290: check constraint
> (SCOTT.CK_TABLE_FIELD) violated". How does this help an end-user
> realise that the field must be a number greater than zero? Is there a
> way of associating a user-defined error message to a constraint? Does
> this mean I need to create a trigger that calls a ‘validate_fieldx'
> function and uses RAISE_APPLICATION_ERROR so a sensible error message
> can be generated? Are there performance implications here?
>
> The 2nd problem with validating a real field such as a postcode. In
> the UK, a postcode pattern can be defined in Perl or VB as
> "[A-Z]{1,2}\d{1,2} \d[A-Z]{2}". It seems the Oracle DB does not
> support regular expressions, so what would people suggest? A complex
> trigger, I'm guessing.
>
> The 3rd problem lies with the user data-entry validation model (2-tier
> or n-tier). I believe that the database should be protected, i.e.
> constraints should be in place in the database to ensure data
> integrity. I would not rely on middle tier or client tier checks to
> protect my database. I also think that the UI should be user friendly.
> I.e. providing masks, dropdowns, and sensible error messages at field
> level. And finally, I don't want to have more than one definition of
> my checks, as that would be a maintenance headache. I've never seen a
> good solution to this but I'm always hoping…

You can catch the error, or alternatively name your constraints with some info for the user, for example

add constraint "VALUE_MUST_BE_POSTIVE" check (value > 0)

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."