Sequence within parent

The sequence within parent column is the column in a detail table that, together with the foreign key column to the master table, constitutes a unique key of the detail table. And sometimes even the primary key if you are not into surrogate keys. A small example of a sequence within parent:

rwijk@ORA11G> create table orders
  2  ( id          number(10) not null primary key
  3  , customer_id number(10) not null
  4  , order_date  date       not null
  5  )
  6  /

Tabel is aangemaakt.

rwijk@ORA11G> create table order_lines
  2  ( order_id               number(10)   not null references orders(id)
  3  , sequence_within_parent number(6)    not null
  4  , product_id             number(10)   not null
  5  , quantity               number(6)    not null
  6  , price_per_piece        number(10,2) not null
  7  , primary key (order_id,sequence_within_parent)
  8  )
  9  /

Tabel is aangemaakt.

The purpose is to let the sequence within parent column start with the value 1 for each order_id and increase it by 1 for each subsequent order_line within the same order. In the days of Designer/2000 you could mark a column with a so-called "Autogen Type" as a sequence within parent, and the Oracle*Forms Generator of Designer would conveniently generate a Pre-Insert trigger at the block level containing a call to a function containing this piece of code:

DECLARE
   CURSOR C IS
     SELECT nvl(max(SEQUENCE_WITHIN_PARENT),0) + 1
     FROM   ORDER_LINES OLE
     WHERE  OLE.ORDER_ID = P_ORDER_ID;
 BEGIN
   OPEN  C;
   FETCH C INTO P_SEQUENCE_WITHIN_PARENT;
   CLOSE C;
 END;

But the problem with this code is that it won't function in a multi user environment, because if this code is executed in two different sessions for the same order_id, both sessions would get the same sequence_within_parent. One would succeed and the other would fail because of the unique index on (order_id,sequence_within_parent). Also, and probably more important, the sequence within parent makes it necessary to execute a select statement to be able to insert a new row. Which harms performance.

So why use the sequence within parent? Are you really going to query the third order_line for order_id 1? I doubt it. In my experience, the sequence within parent is often lightheartedly introduced by the designer "just to be able to have an easy unique identifier". The only case I can see where this column is useful, is when it is important to be able to quickly search for the n-th order_line, and when the total number of detail records within one master record can be a big number. And as said, this case is very rare.

Suppose the tables above are filled like this:

rwijk@ORA11G> insert into orders
  2  select 1, 1234, date '2008-01-01' from dual union all
  3  select 2, 5678, date '2008-01-20' from dual union all
  4  select 3, 1234, date '2008-02-01' from dual
  5  /

3 rijen zijn aangemaakt.

rwijk@ORA11G> insert into order_lines
  2  select 1, 1, 987, 10, 25 from dual union all
  3  select 1, 2, 876, 1, 500 from dual union all
  4  select 1, 3, 765, 50, 2 from dual union all
  5  select 2, 1, 654, 1, 750 from dual union all
  6  select 3, 1, 99, 1, 22.95 from dual union all
  7  select 3, 2, 98, 1, 67.99 from dual union all
  8  select 3, 3, 97, 5, 9.99 from dual union all
  9  select 3, 4, 96, 1, 19.99 from dual union all
 10  select 3, 5, 95, 10, 24.95 from dual
 11  /

9 rijen zijn aangemaakt.

An insert, placed in an api of course, would be something like this:

rwijk@ORA11G> insert into order_lines
  2  ( order_id
  3  , sequence_within_parent
  4  , product_id
  5  , quantity
  6  , price_per_piece
  7  )
  8  values
  9  ( 3
 10  , (select nvl(max(sequence_within_parent),0)+1 from order_lines where order_id=3)
 11  , 90
 12  , 1
 13  , 4.99
 14  )
 15  /

1 rij is aangemaakt.

rwijk@ORA11G> select *
  2    from order_lines
  3   where order_id = 3
  4  /

  ORDER_ID SEQUENCE_WITHIN_PARENT PRODUCT_ID   QUANTITY PRICE_PER_PIECE
---------- ---------------------- ---------- ---------- ---------------
         3                      1         99          1           22,95
         3                      2         98          1           67,99
         3                      3         97          5            9,99
         3                      4         96          1           19,99
         3                      5         95         10           24,95
         3                      6         90          1            4,99

6 rijen zijn geselecteerd.

The alternative is to use a sequence based id column and have a unique key - the unique identifier in ERD terms - on (order_id,product_id). Like this:

rwijk@ORA11G> drop table order_lines
  2  /

Tabel is verwijderd.

rwijk@ORA11G> create table order_lines
  2  ( id                     number(10)   not null primary key
  3  , order_id               number(10)   not null references orders(id)
  4  , product_id             number(10)   not null
  5  , quantity               number(6)    not null
  6  , price_per_piece        number(10,2) not null
  7  , unique (order_id,product_id)
  8  )
  9  /

Tabel is aangemaakt.

rwijk@ORA11G> insert into order_lines
  2  select 1, 1, 987, 10, 25 from dual union all
  3  select 2, 1, 876, 1, 500 from dual union all
  4  select 3, 1, 765, 50, 2 from dual union all
  5  select 4, 2, 654, 1, 750 from dual union all
  6  select 5, 3, 99, 1, 22.95 from dual union all
  7  select 6, 3, 98, 1, 67.99 from dual union all
  8  select 7, 3, 97, 5, 9.99 from dual union all
  9  select 8, 3, 96, 1, 19.99 from dual union all
 10  select 9, 3, 95, 10, 24.95 from dual
 11  /

9 rijen zijn aangemaakt.

rwijk@ORA11G> create sequence order_lines_seq start with 10 increment by 1
  2  /

Reeks is aangemaakt.

The insert now becomes much easier:

rwijk@ORA11G> insert into order_lines
  2  ( id
  3  , order_id
  4  , product_id
  5  , quantity
  6  , price_per_piece
  7  )
  8  values
  9  ( order_lines_seq.nextval
 10  , 3
 11  , 90
 12  , 1
 13  , 4.99
 14  )
 15  /

1 rij is aangemaakt.

It is simple, scalable and always works in a multi-user environment.
And in case the end user, REALLY likes to see a "sequence within parent" column in his gui, then you just define a view like this:

rwijk@ORA11G> create view v_order_lines
  2  as
  3  select order_id
  4       , row_number() over (partition by order_id order by id) sequence_within_parent
  5       , product_id
  6       , quantity
  7       , price_per_piece
  8    from order_lines
  9  /

View is aangemaakt.

rwijk@ORA11G> select *
  2    from v_order_lines
  3   where order_id = 3
  4  /

  ORDER_ID SEQUENCE_WITHIN_PARENT PRODUCT_ID   QUANTITY PRICE_PER_PIECE
---------- ---------------------- ---------- ---------- ---------------
         3                      1         99          1           22,95
         3                      2         98          1           67,99
         3                      3         97          5            9,99
         3                      4         96          1           19,99
         3                      5         95         10           24,95
         3                      6         90          1            4,99

6 rijen zijn geselecteerd.