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

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
开发者_如何学Go

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?

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.

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.