Re: Conditional Constraint?

Re: Conditional Constraint?

J Alex wrote:

"DA Morgan" <damorgan@exeso lutions.comwrot e

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_COD E, 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

Re: Conditional Constraint?


"DA Morgan" <damorgan@exeso lutions.comwrot e

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_COD E, 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.

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_COD E, 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