By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,247 Members | 871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,247 IT Pros & Developers. It's quick & easy.

Re: Conditional Constraint?

P: n/a
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 c220 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


wi******@witty.com (wing) wrote in message news:<87**************************@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
Jun 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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" <pl******@mail-online.dkwrote in message
news:25**************************@posting.google.c om...
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 c220 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


wi******@witty.com (wing) wrote in message
news:<87**************************@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

Jun 27 '08 #2

P: n/a
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" <pl******@mail-online.dkwrote in message
news:25**************************@posting.google.c om...
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 c220 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


wi******@witty.com (wing) wrote in message
news:<87**************************@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

Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.