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

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.

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 --

---

;)

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
)
/

---

:p