Re: Check constraints, the impossible dream?

See my earlier comments but you might in some circumstances get halfway there by intelligent constraint naming

for example

alter table orders
add constraint min_order_value check(order_value > 0);

would raise the error check constraint '<schema>.min_order_value violated', similarly a constraint could be named invalid_postcode (except that AFAIK you can't use regular expressions so you'd need a trigger anyway).

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
"John Ashton" <jashton_at_thunkbox.com> wrote in message
news:3c9f0fe3$1_at_peer1.news.newnet.co.uk...


> "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?

> >

> >

> >

>
>