Forum Stats

  • 3,815,618 Users
  • 2,259,062 Discussions
  • 7,893,186 Comments

Discussions

Categories

Target Table must be Local Table

Ralph Ashley
Ralph Ashley Member Posts: 13 Blue Ribbon
in SQL Developer Data Modeler Discussions

How do I resolve the above error message?

I am trying to amend a relational data model by adding a foreign key and I receive the above error message.

TargetTableMustBeLocalTable.PNG


Tagged:
· Share on TwitterShare on Facebook

Best Answers

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,399 Employee
    Answer ✓

    those two tables are linked/remote tables from another model/design and they are in read only mode - you only can refer to them but cannot change them. You need to change them in the original model.

    Philip

    Ralph Ashley
    · Share on TwitterShare on Facebook
  • Dave Schleis
    Dave Schleis Member Posts: 225 Silver Badge
    Answer ✓

    Hello Ralph

    There really is not enough information provided to give a confident answer to your question, but that won't stop me from trying.

    You imply that you are working with a single relational model and a single design. Is this the case?

    When you import a table from a DB into a relational model, this has to be considered the table's "home" model (and design).

    It is possible to share shadow-copies of tables across models and design by clicking and dragging the tables from the browser to a diagram. These images of the table are designated by the chain link symbol in the upper left of table representation in the diagram.

    A table can only be modified in its home model. This is where it is assumed to have full context. The shadow copies are simply representations of the table as it exists in its home.

    In your example, when you draw a foreign key in the diagram, SDDM tries to modify the target table, but it cannot because it not in its home model, hence the error.

    You can create modifiable copies of tables by right-clicking and selecting copy, and then pasting in another model, but when you do this, you are creating an independent copy that is no longer tied to its original, imported version.

    hope this helps

    --dave

    Ralph Ashley
    · Share on TwitterShare on Facebook

Answers

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,399 Employee
    Answer ✓

    those two tables are linked/remote tables from another model/design and they are in read only mode - you only can refer to them but cannot change them. You need to change them in the original model.

    Philip

    Ralph Ashley
    · Share on TwitterShare on Facebook
  • Ralph Ashley
    Ralph Ashley Member Posts: 13 Blue Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16791536#Comment_16791536

    Hi Philip,

    I have so many questions now...LOL

    Okay so I lost my design 2 years ago and I had to recreate by importing the schemas from the database.

    All this time I did not really need to update the tables and have been improving the application by breaking the applications into modules therefore not really needing to update tables and relationships up until now.

    I don't have a logical model.

    I started with a relational model and created the physical model.

    So to my understanding (and I am beginning to think that I am wrong...LOL) I am updating the original model/design.

    So, how do I (pretty please with cherries on top) change the model/design from read-only to an editable mode?

    model_1.png

    The 2 tables exist in the params subview.

    WIth my sincere and deepest gratitude for the assistance.

    Ralph

    · Share on TwitterShare on Facebook
  • Ralph Ashley
    Ralph Ashley Member Posts: 13 Blue Ribbon

    Hi Philip,

    So does this mean I would have to create a new rel. model to make changes to the database? Once the model has been made (somehow) read-only?

    Or is this some sort of a bug?

    Cheers

    Ralph

    · Share on TwitterShare on Facebook
  • Dave Schleis
    Dave Schleis Member Posts: 225 Silver Badge
    Answer ✓

    Hello Ralph

    There really is not enough information provided to give a confident answer to your question, but that won't stop me from trying.

    You imply that you are working with a single relational model and a single design. Is this the case?

    When you import a table from a DB into a relational model, this has to be considered the table's "home" model (and design).

    It is possible to share shadow-copies of tables across models and design by clicking and dragging the tables from the browser to a diagram. These images of the table are designated by the chain link symbol in the upper left of table representation in the diagram.

    A table can only be modified in its home model. This is where it is assumed to have full context. The shadow copies are simply representations of the table as it exists in its home.

    In your example, when you draw a foreign key in the diagram, SDDM tries to modify the target table, but it cannot because it not in its home model, hence the error.

    You can create modifiable copies of tables by right-clicking and selecting copy, and then pasting in another model, but when you do this, you are creating an independent copy that is no longer tied to its original, imported version.

    hope this helps

    --dave

    Ralph Ashley
    · Share on TwitterShare on Facebook
  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,399 Employee

    Hello Ralph,

    in table dialog you can see the location of the source design for those remote objects. Also model ID and object ID are visible there - if source design is open at the same time the name of model and object will appear there.

    remote_object.png


    If source design is really lost and you want to transform the model to editable state then you have two options:

    1) follow what @Dave Schleis advised - Ctrl-A to select all objects, use Copy and "paste" them in another design

    2) you can use following script to nullify remote object ID for your tables. Well it's good to make a copy of your design before that. bound the script to relational model

    Philip

    tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++){
 table = tables[t];
 name = table.getName().toUpperCase();
 table.setRemoteObjectID("");
 columns = table.getElements();
 size = table.getElementsCollection().size();
 for (var i = 0; i < size; i++) {
   column = columns[i];
   column.setRemoteObjectID("");
   column.setDirty(true);
 }
 table.setDirty(true);
 keys = table.getKeys();
 for (var i = 0; i < keys.length; i++) {
 key = keys[i];
 key.setRemoteObjectID("");
 table.setDirty(true);
   if(key.isFK()){
    key.getFKAssociation().setRemoteObjectID("");
    key.getFKAssociation().setDirty(true);
  }
 }
}
    Ralph Ashley
    · Share on TwitterShare on Facebook
  • Ralph Ashley
    Ralph Ashley Member Posts: 13 Blue Ribbon

    Hi Philip,

    Firstly, thank you so much for taking the time to help me, absolutely appreciate it mate.

    Yes I am working with a single relational model and a single design.

    It seems our versions of Data Modeler differs.

    Table summary.png

    I am using SQL Developer 18.4.0.376 build 376.1900 and using the inbuilt Data Modeler.

    This work "1) follow what @Dave Schleis advised - Ctrl-A to select all objects, use Copy and "paste" them in another design"

    I have not tried "2) you can use following script to nullify remote object ID for your tables. Well it's good to make a copy of your design before that. bound the script to relational model" because I don't know where to run the script in data modeler.

    My sincere appreciation for the help Philip.

    · Share on TwitterShare on Facebook
  • Ralph Ashley
    Ralph Ashley Member Posts: 13 Blue Ribbon

    While @Dave Schleis solution worked to copy the entire schema or relational diagram, it however, is not useful if I am sharing tables across multiple sub-views because it create versions of that table.

    So I will have to try 

    tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++){
 table = tables[t];
 name = table.getName().toUpperCase();
 table.setRemoteObjectID("");
 columns = table.getElements();
 size = table.getElementsCollection().size();
 for (var i = 0; i < size; i++) {
   column = columns[i];
   column.setRemoteObjectID("");
   column.setDirty(true);
 }
 table.setDirty(true);
 keys = table.getKeys();
 for (var i = 0; i < keys.length; i++) {
 key = keys[i];
 key.setRemoteObjectID("");
 table.setDirty(true);
   if(key.isFK()){
    key.getFKAssociation().setRemoteObjectID("");
    key.getFKAssociation().setDirty(true);
  }
 }
}

    Question is how and where do I run the script from?

    · Share on TwitterShare on Facebook
  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,399 Employee

    May be it's good to start with Oracle learning library and to search "data modeler" there - https://apexapps.oracle.com/pls/apex/f?p=44785:OLL_SEARCH:107533123398319:::2:P2_SEARCH_TEXT:data%20modeler

    Starting point for scripting is file SQLDeveloperDataModelerScripting.docx located in datamodeler\datamodeler\xmlmetadata\doc directory of DM installation

    · Share on TwitterShare on Facebook
  • Dave Schleis
    Dave Schleis Member Posts: 225 Silver Badge

    Ralph,

    Scripting within SQL Developer Data Modeler is a hidden treasure. I did a webinar on it a few years ago, if you are interested.

    You can find it here.

    hope this helps

    --dave

    · Share on TwitterShare on Facebook
  • Ralph Ashley
    Ralph Ashley Member Posts: 13 Blue Ribbon

    Good morning Philip and Dave,

    Thank you once again for the help and the much needed extra resources, I really appreciate it.

    Anyway I better get cracking.

    With appreciation.

    Ralph

    · Share on TwitterShare on Facebook