Conditional Constraint?

[DA Morgan]

wing wrote:
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

What you are trying to do requires a trigger. I would suggest a
BEFORE INSERT AND UPDATE trigger that fires for each row.

I would also suggest that in the future you:
1. List your version and edition of Oracle
2. NEVER EVER post to more than a single usenet group unless you are
trying to get people so angry they won't help you.

Daniel Morgan

[J Alex]

"DA Morgan" <damorgan@exesolutions.com> wrote wing wrote: 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 What you are trying to do requires a trigger.

No, it doesn't. Check constraints work well for this sort of thing. Note
Jonathon Lewis' post with the constraint.

[DA Morgan]

J Alex wrote:
"DA Morgan" <damorgan@exesolutions.com> wrote wing wrote: 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 What you are trying to do requires a trigger. No, it doesn't. Check constraints work well for this sort of thing. Note Jonathon Lewis' post with the constraint.

I missed Jonathan's posting but the request was for:

"All DEM_CODE should carry an integer POINT"

I'd like to see a Check Constraint with an "AND" clause.

Daniel Morgan