How to link foreign key to auto-incremented primary key in Oracle?

Question

I have two tables whose common field is Load_ID. The PK of the first has a one-to-many relationship with the FK of the second. For example:

Table1:
1, someValue

Table2:
1, value1, ...  
1, value2, ... 
1, value3, ...

I've embedded the actual insert statement in an ASP.NET class where the values are taken from user input:

 "INSERT INTO Compare_Header_Table VALUES
(compare_sequence.nextval, //other values are inserted here)

The sequence I'm using to auto-increment the PK is CREATE SEQUENCE compare_sequence; This seems to work even without creating a trigger or specifying any parameters because the sequence defaults to an increment of 1 and an initial value of 1, which is fine for my purposes.

Where I'm running into problems is trying to link the FK from the second table to the PK of the first. I cannot simply use compare_sequence.currval because I have a separate method for inserting into each table, so I'm creating a new connection each time. I don't want to combine these into one method because that will involve changing the logical structure of my C# class, which I'd rather leave intact.

Any suggestions for how to make this work?

You shouldn't be using separate connections for inserts that are supposed to be in a single transaction. — , Feb 19 '13 at 16:55
@a_horse_with_no_name that's good to know for future reference. Originally I was just inserting it into one table, but then it got split into two. To now do all the inserting at once would involve some major restructuring which I'd rather avoid. — user1985189, Feb 19 '13 at 17:06

APC · Answer 1 · 2013-02-19T17:48:52.573

What you can do is use the RETURNING cause to get the assigned value:

 INSERT INTO Compare_Header_Table (ID) VALUES
        (compare_sequence.nextval)
 returning id into some_value;

This some_value is a local variable which you need to pass out of your first class and hand on to the second class. How precisely you do that rather depends on your architecture.

Incidentally I do hope that when you say 'I'm creating a new connection each time' this is some c# jargon and you're not really creating a new database conection each time. Because that's a pretty expensive operation.

The other thing is, if you're in different sessions for creating the parent and child records what happens to the transaction? You'll have to commit each insert (otherwise the new parent record won't be visible to the foreign key validation when you try to insert the child).

hmmm ok maybe I will have to look into revising my C# code in that case. — user1985189, Feb 19 '13 at 17:09

score 1 · Answer 2 · answered Feb 19 '13 at 16:56

If you want the INSERT operations to be done in separate sessions, you'll need to retrieve the generated key from the first statement and pass that in to the method that inserts the data into table2. One way to retrieve the generated key without requiring a database round-trip would be to do something like

INSERT INTO compare_header_table
  VALUES( compare_sequence.nextval,
          ... )
 RETURNING name_of_primary_key_column
      INTO :bind_variable

How to link foreign key to auto-incremented primary key in Oracle?

2 Answers2