Re: Conditional Constraint?

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 Mon Dec 23 2002 - 08:56:19 CST