Conditional Constraint?

[Kenneth Koenraadt]

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@witty.com (wing) wrote in message news:<873e96d6.0212211735.4fd633d5@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

[Richard Foote]

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@mail-online.dk> wrote in message
news:25fb645f.0212230656.2fab3fdc@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@witty.com (wing) wrote in message
news:<873e96d6.0212211735.4fd633d5@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

[DA Morgan]

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@mail-online.dk> wrote in message news:25fb645f.0212230656.2fab3fdc@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@witty.com (wing) wrote in message news:<873e96d6.0212211735.4fd633d5@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