History for Question13

??changed:
-
Question 13

  Alter the ORD_Line table with the **Check Constraint** that qty > 0.  Also 
  make a **Check Constraint** so that dollars has to be greater than or equal 
  to 0.  Test it by attempting to insert and ORD_Line tuple that has a negative 
  qty and a negative dollar amount.  Describe what happened.   Show evidence of 
  your test.

  SQL::

    alter table order_line  add constraint
          qty  check (qty>0) ;

    alter table order_line  add constraint
          dollars check(dollars>=0) ;

  Description:

    In both cases the error message generated is quite specific and points to the field that is causing problems::

      insert into order_line (ordno, lineno, pid, qty, dollars) 
          values ('1099',  1, 'p03',  -600,    540.00) ;

      DB21034E  The command was processed as an SQL statement because it was
      not a valid Command Line Processor command.  During SQL processing it 
      returned: SQL0545N  The requested operation is not allowed because a row
      does not satisfy the check constraint "ADMINISTRATOR.ORDER_LINE.QTY".  
      SQLSTATE=23513



      insert into order_line (ordno, lineno, pid, qty, dollars)
          values ('1099',  1, 'p03',  600,   - 540.00) ;

      DB21034E  The command was processed as an SQL statement because it was 
      not a valid Command Line Processor command.  During SQL processing it 
      returned: SQL0545N  The requested operation is not allowed because a row 
      does not satisfy the check 
      constraint "ADMINISTRATOR.ORDER_LINE.DOLLARS".  SQLSTATE=23513




<hr solid id=comments_below>


mj (Sep 17, 2001 7:30 pm; Comment #1)  --
 Q said he'd do this one.