Re: Conditional Constraint?

Hi Wing,

I'm with Kenneth and the "constraint camp" on this one. This is most appropriately resolved through the use of constraints rather than a trigger. For a number of reasons. It's easier to code and handle error conditions, it's potentially more efficient and the CBO may have more to go on (although admittedly, it's not too smart when it comes to "complex" check constraints).

The only thing I would add to what Kenneth and Jonathon have suggested is that I would personally recommend going for option 1 (2 constraints) rather than option 2 (1 complicated constraint). The reason being that if the constraint were to be violated, if they were handled separately, it would be obvious what the issue was. If you had them grouped together, it might require further investigation to determine the exact cause of the violation.

Merry Xmas everyone !!

Cheers

Richard

"Kenneth Koenraadt" <plovmand_at_mail-online.dk> wrote in message news:25fb645f.0212230656.2fab3fdc_at_posting.google.com...
> Hi Wing,
>
> The utility Oracle offers for that is *check constraints*.
>
> For instance :
>
> ALTER TABLE Demerit ADD CONSTRAINT a1 CHECK (points between 1 and 6);
>
> ALTER TABLE Demerit ADD CONSTRAINT a2 CHECK (dem_code <= 20 or
> dem_code > 20 and points <=3);
>
> Will implement the rules you mention.
>
> Also,
>
> ALTER TABLE Demerit ADD CONSTRAINT a3 CHECK ( c2 between 1 and 6 and
> (c1 <= 20 or c2> 20 and c2 <=3) );
>
> will implement a1 and a2 above in one constraint, but is less
> readable.
>
>
> Read the doc to get the full syntax and full *understanding* of check
> constraints. They are not good for all purposes, in fact misusing them
> is equal to painting yourself into the corner.
>
> Happy Christmas
>
>
> - Kenneth Koenraadt
>
>
>
>
> wingwong_at_witty.com (wing) wrote in message
news:<873e96d6.0212211735.4fd633d5_at_posting.google.com>...
> > Hi,
> >
> > I am new in Oracle and have a query on how to add conditional
> > constraint.
> >
> > Say, I have a simple table Demerit with three fields.
> >
> > Demerit(DEM_CODE, DEM_DES, POINTS)
> > where
> > DEM_CODE (N, 2)
> > DEM_DES (C, 30)
> > POINTS (N, 1)
> >
> > How to add the following constraint?
> >
> > All DEM_CODE should carry an integer POINT between 1 to 6 inclusive,
> > and DEM_CODE greater than 20 should carry no more than 3 POINT.
> >
> > Thanks in advance of any ideas and inputs.
> >
> > Wing
Received on Tue Dec 24 2002 - 00:56:09 CST