Re: Check constraints, the impossible dream?

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3C990373.254F_at_yahoo.com>...
> 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 <connor_mcdonald_at_yahoo.com> wrote in message news:<3C990373.254F_at_yahoo.com>...
> 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

Well, yes in this particular example is kinda works, except that if the user is updating more than 1 table with a more than 1 field, my constraint failure will just say "ORA-02290: check constraint (SCOTT.VALUE_MUST_BE_POSTIVE) violated". The user doesn't know what field or table. If I add the field and table to the description (e.g. ORDER_ITEM_QUANTITY_MUST_BE_POSTIVE) it'll be too long for Oracle (max 30 chars for contraint names). What about more complex scenarios such as a range? VALUE_MUST_BE_GREATER_THAN_10_AND_LESS_THAN_20? Received on Thu Mar 21 2002 - 07:40:21 CST