Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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

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 re-query 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-user environment this is going to get very messy very quickly, so don't do it!

CURRVAL

In addition to NEXTVAL, sequences have the CURRVAL attribute that returns the current value of the sequence. The following example populates the primary key columns using the NEXTVAL of the relevant sequence, but uses CURRVAL to populate the dependent foreign key column.

BEGIN
  -- Use NEXTVAL to populate the master table.
  INSERT INTO orders (id, description)
  VALUES (orders_seq.NEXTVAL, 'Dummy order description.');
  
  -- Use CURRVAL to populate the FK link in the detail table.
  INSERT INTO order_lines (id, order_id, description)
  VALUES (order_lines_seq.NEXTVAL, orders_seq.CURRVAL, '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.

Query the sequence value first

Another 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.
         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>

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

Return the value from the master record

Another 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.
         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>

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

This method is very useful when triggers are used to recreate the AutoNumber And Identity Functionality seen in other engines. This is because the developer may not know which sequence is used to populate the ID values behind the scenes. 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.
         5 Dummy order description.

5 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
       104          5 Dummy order line description

5 rows selected.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.