Re: Conditional Constraint?

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Tue, 24 Dec 2002 06:52:44 -0800
Message-ID: <3E0874BC.B0FE859B_at_exesolutions.com>


Richard Foote wrote:

> 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

Now that I can see the examples ... I too would agree. Go with the constraint not the trigger.

Dan Morgan Received on Tue Dec 24 2002 - 15:52:44 CET

Original text of this message