1 Reply Latest reply on Apr 18, 2016 9:32 AM by thatJeffSmith-Oracle

    Oracle SQLDevloper: how to export table with self referencing foreign key

    1460754

      I have got a table that i´d like to export with the sql developer in order to be able to restore the data that i am trying to change.

      For this i am using the feature of the Oracle SQLDeveloper where i can create an insert script right out of the data.
      But i am Struggeling to get this Script to work, because it does get executed on a table with a self referencing Foreign key, which does represent a hierarchy.

      A dummy table could be looking like this

      primary_key_id     self_fk 
      1                  null
      2                  1
      3                  1
      4                 2
      5                 3

      But the result of the script does look like this for me after exporting:

      Insert into dummy_table(primary_key_id,self_fk) values(4,2); 
      Insert into dummy_table(primary_key_id,self_fk) values(5,3);
      Insert into dummy_table(primary_key_id,self_fk) values(1,null);
      -- And so on

      but it should look like this in order to just create elements where the foreign key reference should already exist:

      Insert into dummy_table(primary_key_id,self_fk) values(1,null); 
      Insert into dummy_table(primary_key_id,self_fk) values(2,1);
      Insert into dummy_table(primary_key_id,self_fk) values(4,2);
      -- And so on

      So whilst exporting the data as an insert script the order of the inserts is incorrect.
      As a result i am getting the error message that the foreign key does not exist yet.
      So is there any option whilst exporting the data to tell the SQL Developer that it should keep the self referencing foreign key order in mind and create the insert according to the hierarchy?

       

      Nachricht geändert durch 1460754, Versuch zu formatieren