DBA Hub

📋Steps in this guide1/5

Populating Master-Detail Foreign Key Values Using Sequences

This article presents some safe methods for populating master-detail foreign key columns when using sequences.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

Build a test schema

First we need to build a test schema with a master-detail relationship. Notice, we've altered the starting value of the second sequence so the data is easier to read.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;
2

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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>
3

CURRVAL

In addition to , sequences have the attribute that returns the current value of the sequence. The following example populates the primary key columns using the of the relevant sequence, but uses to populate the dependent foreign key column. This works fine and it's safe in multi-user environments.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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>
4

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. This works fine and it's safe in multi-user environments.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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>
5

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. 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. With the triggers in place, references to the sequences are no longer needed. For more information see: - INSERT - AutoNumber And Identity Functionality Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
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>

-- 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;
/

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!