Re: Conditional Constraint?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • DA Morgan
    #1

    Re: Conditional 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

    Tags: None
    • J Alex
      #2
      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.


        Comment

        • DA Morgan
          #3
          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

            Comment

            Previous template Next
            Working...