Re: Check constraints, the impossible dream?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1016789197.26136.0.nnrp-10.9e984b29_at_news.demon.co.uk...
>
> 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.

Thanks, Jonathan, I was beginning to regret my posting altogether for a while there...

> 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
[.. snip..]

Yeah, I was coming to that conclusion myself. I was surprised that Oracle hasn't addressed what I thought would be a common issue for developers. I guess I was kinda hoping that I'd missed some salient 'check constraint' feature that would make life easier. Something where I could simply write (or get an end result of):

alter table employees
add check (postcode like '[A-Z]{1,2}\d{1,2} \d[A-Z]{2}') 'Post code must be standard UK form (e.g. W1 1FG)'

Then I could load this kind of constraint into my GUI at run time and have access to the constraint and error message for immediate field validation. I would also have a nice error message and flexible constraint for PL/SQL, SQL*LOADER inserts/updates (makes for nicer logging, for example).

Anyway, since that seems to be impossible, my next thought was to create a table of lookups for constraints and triggers (where constraints can't do the job) that constraints error messages, masks and constraints for stored procedures and GUI use. If I want to be terse and efficient I just ignore the table, other wise I can use a PRAGMA EXCEPTION_INIT and SQLERRM parsing with lookups to this table for stored procedures and triggers. For regex I can use ORO software's regex in Java. I can also read the appropriate info from the table into VB for the fields I'm interested in validating 'helpfully' in the GUI. I could even make the table a PL/SQL temp table which should make things quicker. I'd need some procedure in place to ensure lookups for constraints/triggers are placed in the table but...

What do you think?

Anyway, thanks for your response. It was useful and I appreciate it.

> 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?
>
>
>
Received on Mon Mar 25 2002 - 05:33:32 CST