How to insert an n:m-relationship with technical primary keys generated by a sequence?

Question

Let's say I have two tables with several fields and in every table there is a primary key which is a technical id generated by a database sequence:

table1             table2
-------------      -------------
field11  <pk>      field21  <pk>
field12            field22

field11 and field21 are generated by sequences.

Also there is a n:m-relationship between table1 und table2, designed in table3:

table3
-------------
field11  <fk>
field21  <fk>

The ids in table1 und table2 are generated during the insert statement:

INSERT INTO table1 VALUES (table1_seq1.NEXTVAL, ...
INSERT INTO table2 VALUES (table2_seq1.NEXTVAL, ...

Therefore I don't know the primary key of the added row in the data-access-layer of my program, because the generation of the pk happens completely in the database.

What's the best practice to update table3 now? How can I gain access to the primary key of the rows I just inserted?

Answer 1

Don't you have another (non surrogate) candidate key for these tables? In general it's not very useful to make a surrogate key as the only key of any table. Assuming you do have some other key then you can use that to retrieve the rows you insert.

Answer 2

If you face this problem and you use the Ibator Ibatis framework as DAO-layer (like we do) you can handle this case by using the <generated-key>-tag in the ibator.config.

In general you solve this problem by doing a

SELECT sequencename.nextval FROM tablename

on the table named tablename using the sequence sequencename to get the next key for the values to insert.

PS: There is no need to pay attention on transactionhandling here, because the next call of the nextval-function will return the next sequencevalue.