Insert values two dependent tables Oracle Apex

0
votes

I've created a query out of 3 dependent tables:

  1. car parts(id, name, value);

  2. car_model(id, name,price);

  3. part_and_model(car_model_id, car_part_id)

I get my car models from a list of values, and for car_id I use sequence for increment.

I have all the needed fields from my query, but now I want to insert data into car_parts and part_and_model at the same time. I've created two processes on submit to insert (the first for car_parts(fk) and the second for part_and_model). When I run this, I get this error and no data gets inserted into the tables:

ORA-02291: integrity constraint (PRACTICE.FK_CAR_PART_car_part) violated - parent key not found

When I delete the second process, the data is correctly inserted into the car_parts table. Why is this happening? Does the second process execute before first? What else am I missing?

item P8_CAR_MODEL = LOV; item P8_ID=>sequence: SELECT CP_SEK.NEXTVAL FROM DUAL;

process1:insert into CAR_PARTS(CP_ID,CP_NAME,CP_PRICE) VALUES (:P8_ID, :P8_NAME, :P8_PRICE);

process2: insert into part_and_model(PART_ID, MODEL_ID) values (:P8_ID,:P8_CAR_MODEL);

EDIT:

I managed to insert into car_parts table, but the error still says that the parent cannot be found (even though) it's in the table, so it must be the sequence... here is my code: process1: begin select PART_SEK.NEXTVAL INTO :P8_ID from dual;

insert into car_parts(id,name,value) VALUES (:P8_ID, :P8_NAME, :P8_PRICE); end;

process2:

insert into part_and_model(car_model_id, car_part_id) values (:P8_MODEL,:P8_ID);

EDIT2: when i use PART_SEK.currval instead of p8_id in second process, it works Once again, thank you all for your time.

3
sqloracleoracle-apex
As littlefoot said, do both the inserts in a single process. But I had one comment I wanted to add. If you don't really need the item P8_ID to be viewed before the insert(which you most likely don't), you should declare it and assign it its value in the process just before inserting. Because if its the source of an item, every time you load, the sequence triggers. So your DB will have gaps in the CP_ID(values going like 1, 3, 4, 8, 13,..) This doesn't really break anything, it's just nicer if they are perfectly sequential. – TineO

3 Answers

1
votes

Insert rows into both tables in the same process; master table first, details next.

0
votes

Look at the sequence of the processes. The first process must have a lower sequence e.g 10 and the second must be greater. Be sure that you in the first process you have to insert into the master table. Change the processes execution point to processing.

0
votes
DECLARE
  a_id NUMBER;
BEGIN
  a_id := your_sequence.nextval;

  INSERT INTO car_parts (cp_id, cp_name, cp_price)
  VALUES (a_id, :P8_NAME, :P8_PRICE);

  INSERT INTO part_and_model (part_id, model_id)
  VALUES (a_id, :P8_CAR_MODEL);
END;

By all rights this should work. I do believe your problem really is with the sequence, nothing else I can think of that would cause your error. So please try this and let us know.