Re: Conditional Constraint?

"J Alex" <jalexanderssd_at_yahoo.com> wrote in message news:aIjN9.107454$Db4.2983776_at_twister.tampabay.rr.com...
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote
> > As soon as you start doing conditional constraints, you're talking
> business
> > logic. At which point, your attention invariably should turn the way of
a
> > before insert and/or before update trigger.
> >
> > The simple one, that all demerit points should be between 1 and 6 is a
> check
> > constraint:
> >
> > create table blah (
> > col1 number);
> > alter table blah add (constraint blah_col1_ck check (col1 between 1 and
> 6));
> >
> > But as soon as you start wanting to say "if this then that', it's
trigger
> > time.
> Check constraints can include AND and OR logic, so a trigger isn't
required
> here.

Of course they can, and it is possible that this specific example could indeed be coded with 'just' a check constraint (though I worried about whether there was more complexity behind the original post than was evident). But as things get more conditional, *in principle* it's time to start looking at triggers. Perhaps I wasn't being as clear as I should have been.

For example, if you want a check constraint that says 'if the value of X for this row is more than 10% of the average of X for all rows', then forget it: column names mentioned in a check constraint can refer only to the values of those columns for the single row being evaluated, so you can't compute an average as part of a constraint definition.

That sort of thing.

HJR. Received on Sun Dec 22 2002 - 13:01:45 CST