#<pre>
/*
   file tests the FK PK constraints

   in phpMyAdmin file will stop at first error

   future upgrade will write procedure to catch exception
   and generate a list of message

   after import
   test fk constraints restrict delete fruit 2, and 4 
   test constraint cascade delete order 25 just added
   test orderLine primary key insert 23, 2, 200
   can preview trigger insert orderID, customerID 26,130;
   26,100 will work

   @author Professor Tom DeDonno
   @date   07/23/2015

 */
#insert order 25, then later test cascade on delete
insert into orders (orderID, customerID) 
VALUES (25, 120 );

insert into orderLine (orderID, fruitID, quantity )
values (25, 2, 200 ), (25, 3, 200 );

#need to add 4 to test restrict orderLine to fruit
insert into orderLine( orderID, fruitID, quantity )
values ( 24, 4, 200 );

#fails 2 pear is in inventory
delete from fruit where fruitID = 2;

/** its easier to use PMA interface, but here are test scripts */
#fails 4 is in orderLine
delete from fruit where fruitID = 4;

# test cascade delete
delete from orders where orderID = 25;

# note 200 inserts into orderLine are temporary tests
delete from orderLine where quantity = 200;

# note dual primary key this will fail
insert into orderLine (orderID, fruitID, quantity )
values (23, 2, 200 );

# triggers will discuss why this fails (you can test it)
insert into orders (orderID, customerID) VALUES (26, 130);

#</pre>