Populating Master-Detail Foreign Key Values Using Sequences

For most Oracle developers, populating master-details relationships with sequences is a complete no-brainer, but for those who have recently migrated from other environments, such as Access and SQL Server, it can seem a little confusing at first. The examples in this article are written in PL/SQL, but the methods work just as well with other programming languages.

Build a test schema
What not to do!
Query the sequence value first
Return the value from the master record

Build a test schema

First we need to build a test schema with a master-detail relationship.

CREATE TABLE orders (
  id           NUMBER(10),
  description  VARCHAR2(50),
  CONSTRAINT orders_pk PRIMARY KEY (id)
);

CREATE SEQUENCE orders_seq;

CREATE TABLE order_lines (
  id           NUMBER(10),
  order_id     NUMBER(10),
  description  VARCHAR2(50),
  CONSTRAINT order_lines_pk PRIMARY KEY (id),
  CONSTRAINT orli_orde_fk FOREIGN KEY (order_id) REFERENCES orders(id)
);

CREATE INDEX orli_orde_fk_i ON order_lines(order_id);

CREATE SEQUENCE order_lines_seq START WITH 100;

Notice, we've altered the starting value of the second sequence so the data is easier to read.

What not to do!

At first glance you might think you can requery the maximum ID value from the master table, while populating the detail table.

BEGIN
  -- Populate the master table.
  INSERT INTO orders (id, description)
  VALUES (orders_seq.NEXTVAL, 'Dummy order description.');
  
  -- Requery the master table to populate the FK link in the detail table.
  INSERT INTO order_lines (id, order_id, description)
  VALUES (order_lines_seq.NEXTVAL, (SELECT MAX(id) FROM orders), 'Dummy order line description');
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT * FROM orders ORDER BY id;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Dummy order description.

1 row selected.

SQL> SELECT * FROM order_lines ORDER BY id;

        ID   ORDER_ID DESCRIPTION
---------- ---------- --------------------------------------------------
       100          1 Dummy order line description

1 row selected.

SQL>

It appears to work, but in a multi-usr environment this is going to get very messy very quickly, so don't do it!

Query the sequence value first

One option is to query the sequence value before inserting the master record. This way you have the appropriate sequence value ready for the foreign key column in the detail record.

DECLARE
  l_order_id  orders.id%TYPE;
BEGIN
  -- Select the next sequence value.
  SELECT orders_seq.NEXTVAL
  INTO   l_order_id
  FROM   dual;

  -- Use the value to populate the master table.
  INSERT INTO orders (id, description)
  VALUES (l_order_id, 'Dummy order description.');
  
  -- Reuse the value to populate the FK link in the detail table.
  INSERT INTO order_lines (id, order_id, description)
  VALUES (order_lines_seq.NEXTVAL, l_order_id, 'Dummy order line description');
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT * FROM orders ORDER BY id;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Dummy order description.
         2 Dummy order description.

2 rows selected.

SQL> SELECT * FROM order_lines ORDER BY id;

        ID   ORDER_ID DESCRIPTION
---------- ---------- --------------------------------------------------
       100          1 Dummy order line description
       101          2 Dummy order line description

2 rows selected.

SQL>

This works fine and it's safe in multi-user environments.

Return the value from the master record

An alternative is to return the ID value during the insertion of the master record, so it's available during the insertion of the detail record.

DECLARE
  l_order_id  orders.id%TYPE;
BEGIN
  -- Populate the master table, returning the sequence value.
  INSERT INTO orders (id, description)
  VALUES (orders_seq.NEXTVAL, 'Dummy order description.')
  RETURNING id INTO l_order_id;
  
  -- Use the returned value to populate the FK link in the detail table.
  INSERT INTO order_lines (id, order_id, description)
  VALUES (order_lines_seq.NEXTVAL, l_order_id, 'Dummy order line description');
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT * FROM orders ORDER BY id;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Dummy order description.
         2 Dummy order description.
         3 Dummy order description.

3 rows selected.

SQL> SELECT * FROM order_lines ORDER BY id;

        ID   ORDER_ID DESCRIPTION
---------- ---------- --------------------------------------------------
       100          1 Dummy order line description
       101          2 Dummy order line description
       102          3 Dummy order line description

3 rows selected.

SQL>

Once again, it works fine and it's safe in multi-user environments.

This method can also be used when triggers are used to recreate the AutoNumber And Identity Functionality seen in other engines. To see this in action create two triggers to populate the ID columns.

-- Create triggers to support autonumber functionality.
CREATE OR REPLACE TRIGGER orders_bir 
BEFORE INSERT ON orders 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT orders_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

CREATE OR REPLACE TRIGGER order_lines_bir 
BEFORE INSERT ON order_lines 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT order_lines_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

With the triggers in place, references to the sequences are no longer needed.

DECLARE
  l_order_id  orders.id%TYPE;
BEGIN
  -- Populate the master table, returning the sequence value.
  INSERT INTO orders (description)
  VALUES ('Dummy order description.')
  RETURNING id INTO l_order_id;
  
  -- Use the returned value to populate the FK link in the detail table.
  INSERT INTO order_lines (order_id, description)
  VALUES (l_order_id, 'Dummy order line description');
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT * FROM orders ORDER BY id;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Dummy order description.
         2 Dummy order description.
         3 Dummy order description.
         4 Dummy order description.

4 rows selected.

SQL> SELECT * FROM order_lines ORDER BY id;

        ID   ORDER_ID DESCRIPTION
---------- ---------- --------------------------------------------------
       100          1 Dummy order line description
       101          2 Dummy order line description
       102          3 Dummy order line description
       103          4 Dummy order line description

4 rows selected.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.