Question13

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

mj (Sep 17, 2001 7:30 pm; Comment #1)

Q said he'd do this one.