/*=====
   set up inventory and orders tables for trigger examples
   (includes bonus: naming of primary and foreign key 
   constraints)

   last modified: 2016-12-01
=====*/

/*=====
   set up tables for trigger example
=====*/

-- set up an inventory table
-- (bonus aspect: giving a name to the primary key, which is
-- actually a constraint object)

drop table inventory cascade constraints;

create table inventory
(item_num                integer,
 item_name               varchar2(15),
 item_quantity           integer                default(0),
 item_price              decimal(5,2),
 constraint inventory_pk primary key(item_num));

-- set up a simple orders table

drop table orders cascade constraints;

create table orders
(order_num      varchar2(3),
 cust_name      varchar2(20),
 item_num       integer         not null,
 order_quantity integer         default(1),
 constraint     orders_pk       primary key(order_num),
 constraint     inventory_orders_fk 
                foreign key     (item_num)      references inventory);

-- end of inventory-orders-setup.sql