How To: Generate an ERD for Selected Tables in SQL Developer

thatjeffsmith SQL Developer 108 Comments

Tell Others About This Story:

Entity Relationship Diagrams tell a story. An employee is assigned one department. A location can house one or more departments. Jeff doesn’t have a boss because he is ‘the man.’ It can be challenging to understand the nature of your data by simply reading through the list of Foreign Key constraints for a table. Who wants to read this

Foreign keys - they do exist!

when you can instead see this

A pretty picture is worth a few dozen DDL scripts?

So how do you go from one to the other?

Today it requires just a few steps:

Open the Data Modeler Browser in SQL Developer

View .. Data Modeler .. Browser

This will give you a new panel, underneath your Connections|Reports panel.

Go to the Relational Models node in the Tree

Right-mouse-click, ‘New Relational Model’

This will give you a blank model workspace.

Select your table(s) from the connection tree and drag them to the model space.

Voila, instant ERD! The only problem is that you will need to KNOW which objects to drag and drop. The Data Modeler will not automatically recursively search for related objects by foreign key constraint and add those tables. However, if you do add tables one at a time, it will auto-generate the relationship if there is one defined in the database.

I know what you’re thinking, ‘Shouldn’t this be easier?’

Maybe watching the movie will help?

ctrl_drag_model

Stay thirsty my friends!

Update! SQL Developer can automatically add ‘child tables.’

If you hold down CTRL while you drag, it will bring in all related subordinate objects. Thanks for the heads-up Philip (the guy who wrote the code!) For an example, take HR.REGIONS and Ctrl drag it to a model. You’ll get EMPLOYEES, DEPARTMENTS, JOB_HISTORY, etc all auto-added to the model. Pretty slick!

Tell Others About This Story:

Comments 108

  1. Hi,

    I have one question that i have some list of tables and i can query that it shows parent, child tables details. but i want to see the relationship between those tables like one-one , one-many,many-many etc., I want to write a procedure that it should give result as follows:
    Parent table parent-column Child_table, Child_column, Relationship.
    Could you please help me in this asap? your help is more more appreciable. this is an urgent requirement in my project

    Regards,
    Nagalakshmi

    1. thatjeffsmith Post
      Author
  2. This is beautiful and all, but it would be even cooler if I could drop tables from the schema browser into the relational model rather than going through the teeth grinding agony of using the tree from the connections tab.

    1. thatjeffsmith Post
      Author
  3. Is this functionality supposed to include VIEW to TABLE references/dependencies/relationships? If not, is it on the road map for inclusion?

    I’ve inherited a massively complex database with associated code. Often the code for a particular section of functionality has been trimmed out or refactored but I’m left with the detritus of no-longer-needed tables that I want to remove. Trouble is, occasionally there is a view that selects columns from the table (or from another view) and so removing the apparently empty, leaf-node table will break things further upstream. At the moment, I’m having to do the trawling through manually wherever this occurs (which is pretty much everywhere – the original developers liked views a lot)

    SchemaSpy seems to be able to traverse the view links, but the graphs are too big for me to print and I can’t get the regexp working to reduce the clutter. Nothing else seems to read stuff other than the explicit constraint-based information.

    1. thatjeffsmith Post
      Author

      We’re showing relationships and you’re describing dependencies. If you are looking for a graphical layout of the dependencies, we don’t do that, and it’s not currently on the road map.

  4. About tab
    Version 3.0.04
    Build MAIN 04.34

    Version Tab
    Java(TM) Platform 1.6.0_11
    Oracle IDE 3.0.04.34
    Versioning Support 3.0.04.34

    1. thatjeffsmith Post
      Author
  5. Hi Jeff,
    is there a possibility to display table/column COMMENTS (in Oracle) metadata information in the generated Relational Model? I know this isn’t a “standard” but it would greatly help to read the diagram.
    Appreciate if you could point me to right direction (if there is any).
    Thanks a lot
    Jan

    1. thatjeffsmith Post
      Author
  6. I am a very new user of SQL Developer. I followed your instructions to create an ERD and it worked great. Thank you for the easy to follow instructions. My question is, now that I have the diagram of my db, it looks like a hot mess. I can not figure out how to move the referencial lines around to not cross tables or other lines. I am trying to make the picture more readable for the developers. Can you help me, please?

    1. thatjeffsmith Post
      Author

      You can try a few things – there are some pre-arranged renderings available. Right-click in the diagram space, Layout > Auto Layout > 1-4.

      You can move the lines around AND add elbows (which allows a line to change direction to avoid collisions with other objects) – read about that here.

    2. thatjeffsmith Post
      Author
    3. I have another question. Have been searching the web all day looking for the answer and it all says the same thing. It just doesn’t work for me.

      I built my erd. I moved my lines around so that it is readable and I printed it to a file in pdf format. My file is empty. So I just tried to print it. Blank pages printed. What in the world am I doing wrong?

      I went file>data modeler>print to just print it and
      file>data modeler>print diagram>to pdf file to save it to a file

    4. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      I see you giving lucidchart quite the plug in several different spots. What would you say are the benefits of our modeler to that product and vice versa?

    2. I think what Karren is referring to is the convenience of the Lucidchart. On the sight it talks a lot about drag and drop feature, the inexpensive factor since it is based on the cloud concept and has a collaborative feature. (https://www.lucidchart.com/pages/er-diagram-tool) I am still diving into it, but it seems pretty smooth to work with and I am enjoying it.

  7. Ok, i got it to open, and looks like this version is working much better and no issues so far with memory.

    but in case.

    what are the steps to increase the memory ?

    1. thatjeffsmith Post
      Author
  8. Ok, i managed to the Java.exe file location, but this version looks the same.

    however i am trying to open the dmd file i created but i get the XML code instead of the diagram !

    what am i doing wrong ?

    1. thatjeffsmith Post
      Author
  9. I am using SQL Developer V 3.1.07
    Build Main-07.42

    I am the same person who is having trouble with the memory issue.

    found that i can upgrade to 3.2.2 and after pointing to the Java.exe for the V3.2.2 i am stuck with a screen message = Unable to Launch the Java virtual Machine Located at path C:\…………………. with no options to correct that !

    Back to my older v3.1.07 after finally saving a Data-Modeler.dmd file, when i try to open it, it is a blue screen, and can’t see the diagram.

    1. thatjeffsmith Post
      Author

      In that version you set that flag in the sqldeveloper.conf file in the bin directory.

      And if you get a chance, try to upgrade to version 4.0.1 – lots of improvements over your version.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      Right, I said to run sqldeveloper.exe. The ones with the ‘W’ will run run WITHOUT the cmd/console window.

      Also, your version really out of date. If you upgrade, I’m thinking you’re going to be much happier.

    1. thatjeffsmith Post
      Author
    2. Well, First I have selected the entire set of tables and drag to the model workspace. I got the icon with gears and starting seeing some build up of tables, but it never finished. Had to reboot.

      Then started by dragging three tables, then save the file. worked, but, when i need to add one more table, it goes into not responding mode.

    3. thatjeffsmith Post
      Author
    4. WEll, i tried to follow

      ” Instead of running the ‘sqldeveloper.exe’ file in the root directory, we are going to go several sub-directories down. Find the ‘bin’ sub-directory and run the ‘sqldeveloper.exe’ there.

      When you do this, a CMD window will open, and then you’ll see the SQL Developer application load. ”

      CMD window did not open, SQL Developer did load, i tried to add on e more table, now i am back to square one, where it is frozen, black screen, have no choice but to Ctrl Alt Del and close it.

      You mentioned more memory for the JVM ! Java virtual machine ?
      I am running it from my local machine, unless i did understand what you mentioned.

      What else could be done ?

    5. thatjeffsmith Post
      Author

      You ran sqldeveloper.exe or you ran sqldeveloperW.exe?

      Yes, the Java Virtual Machine – it’s what actually runs your java application. You can tell it to give SQL Developer more memory from the OS. To do that, I”ll need to know which version of SQL Developer you’re running first.

  10. Hi Jeff,

    I am new user to Oracle SQL developer. Till 3 years i was using Oracle SQL plus. Now I have a new database and I need to study it. Now for that I need to get ER diagram to study various constarints to the tables.Can you pls let me know how to get ER diagram for the entire database?..

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
    2. I have my ERD diagram generated, but I see no Print Diagram option available under the File menu.

      I currently have version 3.1.07. Do i need to upgrade for that option?

    3. thatjeffsmith Post
      Author

      It should be like File > Print Diagram > To Image File.

      Of course if you’re running this in SQL Developer, it would be File > Data Modeler > Print Diagram > To Image file.

      I’m not running version 3.1, but that feature has been in the tool since the beginning.

  11. Hi Jeff!

    Here’s the query:

    SELECT table_name, constraint_name, status, owner
    FROM all_constraints
    WHERE r_owner = :r_owner
    AND constraint_type = ‘R’
    AND r_constraint_name in
    (
    SELECT constraint_name
    FROM all_constraints
    WHERE constraint_type IN (‘P’, ‘U’)
    AND table_name = :r_table_name
    AND owner = :r_owner
    )
    ORDER BY table_name, constraint_name

    1. thatjeffsmith Post
      Author

      Ok, but what does this show?

      SELECT table_name, constraint_name, status, owner, constraint_type
      FROM all_constraints
      where table_name = ‘EMPLOYEES’ and owner=’HR’ and constraint_type = ‘R’ — where EMPLOYEES = your table and HR = your schema

      This will show the referential integrity constraints for the selected table, do you have any?

  12. Hi Jeff!

    Please tell me how to list the Foreign Key constraints for a table as it’s shown in the first image.

    1. thatjeffsmith Post
      Author

      Browse to your table in the tree and open it.

      Go to the constraints page.

      This will by default display ALL constraints. What you see in the screenshot is a filtered list of constraints with only ‘Foreign Key’ as the type.

      Right click in the Constraint_Type column header and select ‘Filter Column’ and then select ‘Foreign_Key’ and hit .

      You’ll see the grid refresh with a list of those constraints only, and the column header will now show a ‘funnel’ icon to indicate the grid entries have a filter applied.

    2. Thank you for your answer. The problem is the constraints page doesn´t display all constraints. It shows just primary key and check constraints and I don’t know why doesn’t show foreign keys. Any other suggestion? I’m using version 3.2.20.09.

    3. thatjeffsmith Post
      Author

      Not to be the bearer of bad news, but I’m afraid your table doesn’t have any foreign keys. It’s not uncommon for application developers to do this on purpose with the false belief it improves performance.

    4. In my schema there are hundreds of tables and I’m sure there are plenty of foreign keys. At this moment I’m issuing a query to obtain that information from the data dictionary, but I’d prefer to see it in the SQL Developer GUI.

    5. thatjeffsmith Post
      Author

      Show me the query you’re using to manually query the data dictionary for the foreign key constraints?

      It’s possible we have a bug, but I’d like to see proof that you have physical keys first 🙂

  13. Jeff,
    I have the following Create table from SQL Developer and I am trying to generate an ERD Diagram using 2003 Visio. I was wondering what I need to do to convert the Create Table to an ERD Diagram. Thanks
    CREATE TABLE “TT_TEAM”.”HR_REVOKED_SECURITY_ORGS”
    (
    “ID” NUMBER NOT NULL ENABLE,
    “ACAT_CODE” VARCHAR2(6 BYTE) NOT NULL ENABLE,
    “APPR_SEQ_NO” NUMBER(3,0) NOT NULL ENABLE,
    “USER_ID” VARCHAR2(30 BYTE) NOT NULL ENABLE,
    “ACTION_IND” VARCHAR2(1 BYTE) NOT NULL ENABLE,
    “ACTIVITY_DATE” DATE NOT NULL ENABLE,
    “COAS_CODE” VARCHAR2(1 BYTE),
    “ORGN_CODE” VARCHAR2(6 BYTE),
    “POSN” VARCHAR2(6 BYTE) NOT NULL ENABLE,
    “MANDATORY_APPR_IND” VARCHAR2(1 BYTE),
    “APPR_POSN” VARCHAR2(6 BYTE),
    “ORGN_MANAGER” VARCHAR2(6 BYTE) NOT NULL ENABLE,

    1. thatjeffsmith Post
      Author

      You can create a new model in SQL Developer, then do a Data Modeler – Import – DDL File. Point it to your CREATE TABLE script.

      Or, create the table in the database, then do a Import – Data Dictionary. Point it to the database where you created the table.

  14. In SQL Developer, Is it Viable, to inVoke a Very Visiable View (of a Relational Model etc) Via Visio? (Can SQLDev print in Visio format where the objects kin Visio are populated properly?). For example, the models may be Exportable to some XML format, that Visio can read in ? Thanks … pjr

    1. thatjeffsmith Post
      Author

      It may be possible to have Visio import data from an XML document, but I don’t know. We do not have any explicit support for Visio – importing or exporting.

  15. Hi Again Jeff, I dont see a How To, on “How To Generate a logical model for Selected Tables in SQL Developer” . I cannot seem to drag and drop the tables onto the window to do an auto-gen of the Logical Model

    1. thatjeffsmith Post
      Author

      Logical models have entities, not tables.

      So you’ll drag your table(s) onto your relational model, then engineer that to a logical model. It’s the double blue arrow that points to the left on the main toolbar.

  16. thatjeffsmith Post
    Author
  17. Jeff doesn’t have a boss because he is ‘the man.’…or may be he is too cool to have boss. I am having fun reading all these fun lines you mix.

Leave a Reply

Your email address will not be published. Required fields are marked *