Parent/Child Table Design

From: Digital Logic <>
Date: Tue, 5 Feb 2008 13:10:46 -0800 (PST)
Message-ID: <>

A bit a newbie to Oracle so I apologize if this is a simple question. I need to implement two tables similar to the classic order and order details example. I've place a rough description of the two tables below. How would I autogenerated values for the OrderLineNumber column given that that should be unique for each order and not for each order line. I experimented with using triggers to generated the numbers based on the records already present in the table. I put together a trigger that works as long as you're only insert a single row at a time, but does not work for multiple rows. It would appreciated if anyone has a solution.

  OrderID integer primary key,

  OrderID integer not null,
  OrderLineNumber integer not null,
  Constraint OrderLines_PK
    Primary Key (OrderID, OrderLineID),
  Constraint OrderLines_FK
    Foreign Key (OrderID)
    References Orders(OrderID)
Received on Tue Feb 05 2008 - 15:10:46 CST

Original text of this message