imposing check constraint on table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: imposing check constraint on table

  1. #1
    Join Date
    Jul 2008
    Posts
    7

    imposing check constraint on table

    Tables

    Classes(class, type, country, numguns, bore, displacement)
    Battles(name, date)
    Outcomes(ship, battle, result)
    Ships(name, class, launched)


    If a class of ships has more than 9 guns, then their bore must be no larger than 14 inches.

    How to impose the above condition on table using check constraints

    Thanks in advace

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I do not think you can do it with check constraint.
    I would go with an after insert/update trigger -sorry, you have to actually code the logic.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,476

    Talking Before insert/update

    Maybe try a before insert/update trigger:
    Code:
    -- etc --
      If INSERTING Or UPDATING('BORE') 
      Then
        If :new.bore > 14 and :new.numguns => 9 Then RAISE; End If;
      End If;
    -- etc --
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,476

    Talking Wait!!! Maybe you can:

    Try it this way:
    Code:
    Create Table Classes 
    (
      Class          Varchar2(10)
    , Ship_Type      Char(1)
    , Numguns        Number(3)
    , Bore           Number(3)
    , Displacement   Number(5,1)
    , Constraint     Check_Bore
                     Check ((Bore < 14 And Numguns > 8 )
                        Or  (Bore > 0 And Numguns < 9 ))
                     Enable
    )
    /
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width