8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | 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
- What not to do!
- CURRVAL
- 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 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! It's also going to have performance problems as the amount of data in the parent table increases.
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...