Using primary key of a table as foreign key in insert query-CodePudding

I am trying to populate data into my tables. Let's say I have three tables

TABLE_A, TABLE_B, TABLE_C.

TABLE_A has four columns, I am currently getting values for this table as follows:

INSERT INTO TABLE_A(COL_A, COL_B, COL_C, COL_D) 
    SELECT TABLE_A.SQ.nextval, colb AS COL_B, colc AS COL_C, cold as COL_D 
    FROM TABLE_D

Now I want to use the primary key of Table_A as foreign key in my other table which is TABLE_B, and primary key of TABLE_B as foreign key of TABLE_C.

CodePudding user response：

In the same operation? If so, then you wouldn't directly use sequence value, but first store it into a local variable so that you could use it as a

primary key for table_a
foreign key value while inserting into table_b

The same goes for table_b's primary key (which will then be used as a foreign key in table_c).

CodePudding user response：

The RETURNING INTO clause can be used for this. Below is a simple example for a single row insert with tablea, tableb and tablec.

Note that this is is for a row by row operation, so you'd have to loop through table_d in your case. There is a RETURNING BULK COLLECT INTO as well to populate an array.

create sequence tablea_seq;
create sequence tableb_seq;
create sequence tablec_seq;


create table tablea (
    tablea_id                      number 
                                   constraint tablea_tablea_id_pk primary key,
    c1                             varchar2(10 char)
)
;

create table tableb (
    tablea_id                      number
                                   constraint tableb_tablea_id_fk
                                   references tablea on delete cascade,
    tableb_id                      number 
                                   constraint tableb_tableb_id_pk primary key,
    col1                           varchar2(10 char)
);

create table tablec (
    tableb_id                      number
                                   constraint tablec_tableb_id_fk
                                   references tableb on delete cascade,
    tablec_id                      number 
                                   constraint tablec_tablec_id_pk primary key,
    col1                           varchar2(10 char)
);

DECLARE
  l_tablea_id tablea.tablea_id%TYPE;
  l_tableb_id tableb.tableb_id%TYPE;
BEGIN
  INSERT INTO tablea (tablea_id,c1) VALUES (tablea_seq.NEXTVAL,'some val')
    RETURNING tablea_id INTO l_tablea_id;
  INSERT INTO tableb (tableb_id,tablea_id,col1)
    VALUES (tableb_seq.NEXTVAL,l_tablea_id,'Foobar')
    RETURNING tableb_id INTO l_tableb_id;
  INSERT INTO tablec (tablec_id,tableb_id,col1)
    VALUES (tableb_seq.NEXTVAL,l_tablea_id,'Foobar');
END;
/