ORDER_ITEMS

Description

Columns

Column	Datatype	Nullable	Default value	Comment
ORDER_ID	NUMBER(12,0)	N		Part of concatenated primary key, references orders.order_id.
LINE_ITEM_ID	NUMBER(3,0)	N		Part of concatenated primary key.
PRODUCT_ID	NUMBER(6,0)	N		References product_information.product_id.
UNIT_PRICE	NUMBER(8,2)	Y
QUANTITY	NUMBER(8,0)	Y

Primary key

Primary key	Columns
ORDER_ITEMS_PK	ORDER_ID, LINE_ITEM_ID

Check constraints

Check constraint	Check condition
SYS_C002420	"LINE_ITEM_ID" IS NOT NULL
SYS_C002421	"PRODUCT_ID" IS NOT NULL

Foreign keys

Foreign key	Referenced table	Referenced constraint	Delete rule	Columns
ORDER_ITEMS_ORDER_ID_FK	ORDERS	ORDER_PK	CASCADE	ORDER_ID
ORDER_ITEMS_PRODUCT_ID_FK	PRODUCT_INFORMATION	PRODUCT_INFORMATION_PK	NO ACTION	PRODUCT_ID

Indexes

Index	Type	Uniqueness	Columns
ITEM_ORDER_IX	NORMAL	NONUNIQUE	ORDER_ID
ITEM_PRODUCT_IX	NORMAL	NONUNIQUE	PRODUCT_ID
ORDER_ITEMS_PK	NORMAL	UNIQUE	ORDER_ID, LINE_ITEM_ID
ORDER_ITEMS_UK	NORMAL	UNIQUE	ORDER_ID, PRODUCT_ID

Options

Option	Setting
Clustered	N
Generated by Oracle	N
Index Organized	N
Logging	Y
Partitioned	N
Temporary	N
Nested	N

Triggers

Trigger

Code

INSERT_ORD_LINE

1   :TRIGGER insert_ord_line
2   :  BEFORE INSERT ON order_items
3   :  FOR EACH ROW
4   :  DECLARE
5   :    new_line number;
6   :  BEGIN
7   :    SELECT (NVL(MAX(line_item_id),0)+1) INTO new_line
8   :      FROM order_items
9   :      WHERE order_id = :new.order_id;
10  :    :new.line_item_id := new_line;
11  :  END;