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

thatjeffsmith SQL Developer 106 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 106

  1. Hi,
    Can you please tell me if i can create ERD in views. I know how to create ERD between tables but when i try to create ERD between views i see no relationship. I understand that Views don’t inherit those constraints from their base tables.
    What is the easiest way to create ERD between views? Can you please give me some steps? Thanks

    1. thatjeffsmith Post
      Author

      ERD’s are entity relationship diagrams, they are in the logical model.

      Now, can you have a relational diagram that shows a relationship between a view and something else? Yes, actually. Oracle supports this, and so does the modeler.

      1. Jeff, Do you have any video showing ERD between VIEWS. I tried to create but it didn’t show any relationship. Though there are relationships in the tables. Thanks

        1. thatjeffsmith Post
          Author
  2. I didn’t know you could do this! I’d been using a 3rd party app to create the ERD before. I’m trying this method, but it looks like the process freezes about halfway through ‘adding objects to diagram’ (been frozen about an hour). From what I can see, all the tables are there in the list but there are no relationships shown (I’m certain they exist because I can see them in the other application I was using). Any ideas what I’m doing wrong? There are about 2300 tables so idk if there is some performance issue.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  3. Hi Jeff:

    Always enjoy your blogs. I know this is a very old thread, but I am having issues trying to use data modeler. I have a dozen or so tables from our production schema (that contains 100s of tables) that I would like to put into a data model. I start by dragging my primary table of interest into the model. All is great. I then drag a table related to that table. The foreign key between the table is not in the model. Each table is an island. I thought that as I dragged new tables into my model, the foreign keys between that table and tables already in the diagram would be added to the model.

    Thanks in advance,

    Keith

    1. thatjeffsmith Post
      Author

      that SHOULD work

      have you just tried File > Import > Data Dictionary and grabbing the tables from there?

      Also, do a ctrl+drag on the main driving table, and it will follow the FK’s to auto-grab the related tables for you.

      1. Thanks Jeff. I will try importing from the Data Dictionary. The main issue is when I want to add another table. I am creating this model as part of a refactoring effort. So during analysis I will discover additional tables that need to be added to the diagram. I attempt to drag the new table in and get no foreign keys. 🙁

        1. thatjeffsmith Post
          Author
          1. About
            —–

            Oracle SQL Developer 4.1.3.20
            Version 4.1.3.20
            Build MAIN-20.78

            IDE Version: 12.2.1.0.42.151001.0541
            Product ID: oracle.sqldeveloper
            Product Version: 12.2.0.20.78

            Version
            ——-

            Component Version
            ========= =======
            Oracle IDE 4.1.3.20.78
            Java(TM) Platform 1.8.0_60
            Versioning Support 4.1.3.20.78

            Properties
            ———-

            Name Value
            ==== =====

            Extensions
            ———-

            Name Identifier Version Status Registration Time Initialization Time Total Time
            ==== ========== ======= ====== ================= =================== ==========

          2. thatjeffsmith Post
            Author

            So same version.

            Here’s what I did.

            Open Relational Model.

            Connect to HR.

            drag over departments.

            drag over employees.

            after employees comes in, I also get the FK between them on department_ID.

            Does that work for you?

          3. Jeff:

            I can’t get access to the HR or SCOTT schemas per corporate policy. And I can’t share our schemas for same reason. 🙁

            It is interesting, but I am able to easily perform my duties using the import from the Data Dictionary. 🙂

          4. thatjeffsmith Post
            Author
  4. I tried the feature, I am able to have the entity created but my ERD does not have any relation links, can you tell me what I did wrong or missing any setting?

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
  5. Hi,

    Wondering if some of the functionality such as generating an ER diagram from an existing schema only works for Oracle, not MYSQL even though I can indeed drag and drop tables from a MYSQL db — I just don’t see the relationships showing, no lines between entities and also no columns. The other question is, can I start without a schema a create a diagram from which a schema will be created? Again, can this be done for MySQL?

    Regards,

    Jeff

    1. thatjeffsmith Post
      Author

      for the best MySQL support, you should use MySQL Workbench. We support officially Oracle, SQL Server, and DB2. Using 3rd party JDBC driver for MySQL SHOULD work – what you see on a reverse engineer is what the MySQL JDBC driver supports. IF there are foreign keys, you should see them. We can generate SQL for your diagram, but not specifically for the MySQL dialect – again, you should probably use MySQL Workbench for that.

  6. Thanks Jeff! That was super easy and awesome! I was struggling trying to use Data Modeler before as I was trying to do it the hard way, from Oracle SQL Developer -> File -> Data Modeler -> Import.

    Thanks Again!

    1. thatjeffsmith Post
      Author

      In version 4.1, it gets even easier. Simply open the table, go to the model tab. If you want to do anything more than just view the diagram, click on the modeler button in the toolbar – that will give you the relational design based on those objects.

  7. Hello Jeff,

    Unable to establish a connection with SQL Server. Have left a detailed question in the “Ask a question” section.

    Cheers,
    Avnip

  8. Hi jeff,

    I am able to create ER diagram using sql developer. I am also able to print to pdf. But my pdf file is not showing any data. Please advise how it can be handled

    1. thatjeffsmith Post
      Author

      How exactly did you print to pdf? Using SQL Developer or a pdf printer driver?

      Also, what version of SQL Developer area you using, and how big is your diagram (number of objects)?

  9. Jeff,

    Its very easy to understand and it saved a lot of time.
    Especially the shortcut for getting the reference tables info.
    Thanks a lot. And keep posting 🙂

    1. thatjeffsmith Post
      Author
  10. Oracle supports Primary Keys and Foreign Keys on views, but none of the many case tools I have tried so far is able to draw these entity relationships over views.

    As all objects in a ERD are drawn based on the Data Dictionary, wouldn’t it be natural that views relationships were also represented?

    1. thatjeffsmith Post
      Author
      1. Hey Jeff! Me again!

        I have just tried out the 4.1 beta release of Data Modeler and that new feature about view dependencies on ER Diagrams is great!

        Can’t wait for the final release. I’m keen to see my students getting the hang of this product.

        Thanks a lot!

        1. thatjeffsmith Post
          Author
  11. Hi

    I am able to create the ERD diagram by steps provided above thanks alot for that, But i am facing a new issue while saving the diagram,In File tab print menu is disabled i am using Version 2.1.1.64.
    Help me out.

    Thanks

  12. {DROP TABLE RESIDENT;
    DROP TABLE GUARDIAN;
    DROP TABLE PRODUCT;
    DROP TABLE SALE;
    DROP TABLE DONATION;
    DROP TABLE INCOME;
    DROP TABLE EXPENSES;
    DROP TABLE ACTIVITY;
    DROP TABLE NEEDS;

    ALTER TABLE EXPENSES DROP COLUMN NEED_ID;
    ALTER TABLE EXPENSES DROP COLUMN ACT_ID;
    ALTER TABLE DONATION DROP COLUMN INC_ID;
    ALTER TABLE SALE DROP COLUMN INC_ID;
    ALTER TABLE INCOME DROP COLUMN SALE_CODE;
    ALTER TABLE INCOME DROP COLUMN DON_CODE;
    ALTER TABLE INCOME DROP COLUMN EXP_ID;

    CREATE TABLE RESIDENT
    (
    RES_NUM VARCHAR2 (5) NOT NULL,
    RES_NAME VARCHAR2 (30),
    IC_NUM VARCHAR2 (15),
    RES_GEN VARCHAR2 (2),
    RES_AGE CHAR (5),
    RES_POST VARCHAR2 (5),
    RES_DIST VARCHAR2 (15),
    RES_DATE DATE,
    PROD_ID CHAR (5),
    DON_CODE CHAR (5),
    GD_ICNUM VARCHAR2 (15),
    CONSTRAINT RESIDENT_RESNUM_PK PRIMARY KEY (RES_NUM)
    );

    CREATE TABLE GUARDIAN
    (
    GD_ICNUM VARCHAR2 (40) NOT NULL,
    GD_NAME VARCHAR2 (25),
    GD_ADD VARCHAR2 (35),
    CON_NUM VARCHAR2 (11),
    CONSTRAINT GUARDIAN_GDICNUM_PK PRIMARY KEY (GD_ICNUM)
    );

    /*ALTER TABLE “GUARDIAN” MODIFY (“GD_ICNUM” VARCHAR2 (40));*/

    CREATE TABLE PRODUCT
    (
    PROD_ID CHAR (5) NOT NULL,
    PROD_TYPE VARCHAR2 (20),
    PROD_PRICE NUMBER (4,2),
    SALE_CODE CHAR (5),
    CONSTRAINT PRODUCT_PRODID_PK PRIMARY KEY (PROD_ID)
    );

    CREATE TABLE SALE
    (
    SALE_CODE CHAR (5) NOT NULL,
    TOTAL_SALE NUMBER (5,2),
    SALE_AMOUNT NUMBER (2),
    INC_ID CHAR (5),
    CONSTRAINT SALE_SALECODE_PK PRIMARY KEY (SALE_CODE)
    );

    CREATE TABLE DONATION
    (
    DON_CODE CHAR (5) NOT NULL,
    DON_TYPE VARCHAR2 (20),
    DON_DATE DATE,
    INC_ID CHAR (5),
    CONSTRAINT DONATION_DONCODE_PK PRIMARY KEY (DON_CODE)
    );

    CREATE TABLE INCOME
    (
    INC_ID CHAR (5) NOT NULL,
    SALE_CODE CHAR (5),
    DON_CODE CHAR (5),
    EXP_ID CHAR (5),
    /*CONSTRAINT INCOME_INCID_PK PRIMARY KEY (INC_ID)*/
    PRIMARY KEY (INC_ID)
    );

    CREATE TABLE EXPENSES
    (
    EXP_ID CHAR (5) NOT NULL,
    EXP_TYPE VARCHAR2 (20),
    EXP_DATE DATE,
    NEED_ID CHAR (5),
    ACT_ID CHAR (5),
    CONSTRAINT EXPENSES_EXPID_PK PRIMARY KEY (EXP_ID)
    );

    CREATE TABLE ACTIVITY
    (
    ACT_ID CHAR (5) NOT NULL,
    ACT_TYPE VARCHAR2 (20),
    CONSTRAINT ACTIVITY_ACTID_PK PRIMARY KEY (ACT_ID)
    );

    CREATE TABLE NEEDS
    (
    NEED_ID CHAR (5) NOT NULL,
    NEED_TYPE VARCHAR2 (20),
    CONSTRAINT NEEDS_NEEDID_PK PRIMARY KEY (NEED_ID)
    );

    ALTER TABLE RESIDENT
    ADD CONSTRAINT RESIDENT_PRODID_FK FOREIGN KEY (PROD_ID) REFERENCES PRODUCT (PROD_ID);

    ALTER TABLE RESIDENT
    ADD CONSTRAINT RESIDENT_DONCODE_FK FOREIGN KEY (DON_CODE) REFERENCES DONATION (DON_CODE);

    ALTER TABLE RESIDENT
    ADD CONSTRAINT RESIDENT_GDICNUM_FK FOREIGN KEY (GD_ICNUM) REFERENCES GUARDIAN (GD_ICNUM);

    ALTER TABLE PRODUCT
    ADD CONSTRAINT PRODUCT_SALECODE_FK FOREIGN KEY (SALE_CODE) REFERENCES SALE (SALE_CODE);

    ALTER TABLE SALE
    ADD CONSTRAINT SALE_INCID_FK FOREIGN KEY (INC_ID) REFERENCES INCOME (INC_ID);

    ALTER TABLE DONATION
    ADD CONSTRAINT DONATION_INCID_FK FOREIGN KEY (INC_ID) REFERENCES INCOME (INC_ID);

    ALTER TABLE INCOME
    ADD CONSTRAINT INCOME_SALECODE_FK FOREIGN KEY (SALE_CODE) REFERENCES SALE (SALE_CODE);

    ALTER TABLE INCOME
    ADD CONSTRAINT INCOME_DONCODE_FK FOREIGN KEY (DON_CODE) REFERENCES DONATION (DON_CODE);

    ALTER TABLE INCOME
    ADD CONSTRAINT INCOME_EXPID_FK FOREIGN KEY (EXP_ID) REFERENCES EXPENSES (EXP_ID);

    ALTER TABLE EXPENSES
    ADD CONSTRAINT EXPENSES_NEEDID_FK FOREIGN KEY (NEED_ID) REFERENCES NEEDS (NEED_ID);

    ALTER TABLE EXPENSES
    ADD CONSTRAINT EXPENSES_ACTID_FK FOREIGN KEY (ACT_ID) REFERENCES ACTIVITY (ACT_ID);

    /* Insert RESIDENT rows */
    INSERT INTO RESIDENT VALUES (‘R001′,’AINON BINTI ZAKI’,’520317-07-5890′,’F’,’62’,’33100′,’PENGKALAN HULU’,’31/01/2013′,’40003′,’NULL’,’NULL’);
    INSERT INTO RESIDENT VALUES (‘R002′,’AMINAH BINTI ABU’,’561005-02-5450′,’F’,’58’,’05560′,’ALOR SETAR’,’31/01/2013′,’60001′,’NULL’,’780121-02-5771′);
    INSERT INTO RESIDENT VALUES (‘R003′,’AZIZ BIN SAAD’,’460201-02-5317′,’M’,’68’,’11960′,’BATU MAUNG’,’23/11/2013′,’30001′,’NULL’,’710815-02-6065′);
    INSERT INTO RESIDENT VALUES (‘R004′,’CHANDRAN MURTHY’,’470224-07-5593′,’M’,’67’,’33410′,’LENGGONG’,’27/12/2012′,’30001′,’NULL’,’NULL’);
    INSERT INTO RESIDENT VALUES (‘R005′,’FAUZI BIN ASMUNI’,’430529-06-5123′,’M’,’71’,’08000′,’SUNGAI PETANI’,’27/12/2012′,’50002′,’NULL’,’770805-06-5821′);
    INSERT INTO RESIDENT VALUES (‘R006′,’FAZIDAH BINTI BAHARUDIN’,’510828-01-5038′,’F’,’63’,’11700′,’GELUGOR’,’27/12/2012′,’60002′,’NULL’,’850301-01-5537′);
    INSERT INTO RESIDENT VALUES (‘R007′,’HABIBAH BINTI SAAD’,’510129-08-5394′,’F’,’63’,’13210′,’KEPALA BATAS’,’27/12/2012′,’40003′,’NULL’,’860622-08-5012′);
    INSERT INTO RESIDENT VALUES (‘R008′,’HAFIZAH BINTI SALEH’,’560710-09-5120′,’F’,’58’,’32610′,’SERI ISKANDAR’,’31/1/2013′,’40001′,’NULL’,’781224-09-5468′);
    INSERT INTO RESIDENT VALUES (‘R009′,’HAMID BIN HUSSIN’,’541007-02-5077′,’M’,’60’,’09010′,’KULIM’,’23/11/2013′,’30001′,’NULL’,’731203-02-5021′);
    INSERT INTO RESIDENT VALUES (‘R010′,’HARUN BIN ITAM’,’430917-02-6087′,’M’,’71’,’11500′,’AIR ITAM’,’27/12/2012′,’20003′,’NULL’,’770913-02-6023′);
    INSERT INTO RESIDENT VALUES (‘R011′,’LIM CHENG HANN’,’490122-07-5177′,’M’,’65’,’09100′,’BALING’,’27/12/2012′,’20002′,’NULL’,’871030-07-5313′);
    INSERT INTO RESIDENT VALUES (‘R012′,’IDRIS BIN LABUH’,’420229-08-6323′,’M’,’72’,’33320′,’GRIK’,’3/2/2013′,’20001′,’NULL’,’760914-08-5316′);
    INSERT INTO RESIDENT VALUES (‘R013′,’JUSOH BIN TAIB’,’460404-07-5081′,’M’,’68’,’13500′,’PERMATANG PAUH’,’28/3/2013′,’60002′,’NULL’,’781115-07-5617′);
    INSERT INTO RESIDENT VALUES (‘R014′,’MAHMUD BIN AHMAD’,’420903-07-5225′,’M’,’72’,’13210′,’KEPALA BATAS’,’9/12/2013′,’50001′,’NULL’,’810123-07-6210′);
    INSERT INTO RESIDENT VALUES (‘R015′,’MAZNAH BINTI ZUBIR’,’550326-02-6068′,’F’,’59’,’08340′,’SIK’,’27/12/2012′,’40003′,’NULL’,’830222-02-5175′);
    INSERT INTO RESIDENT VALUES (‘R016′,’NAZRI BIN CHIK’,’491201-02-5441′,’M’,’65’,’11010′,’BALIK PULAU’,’27/12/2012′,’30001′,’NULL’,’850909-02-5209′);
    INSERT INTO RESIDENT VALUES (‘R017′,’NG MEI LIN’,’540410-11-5204′,’F’,’60’,’32600′,’BOTA’,’6/3/2013′,’60001′,’NULL’,’NULL’);
    INSERT INTO RESIDENT VALUES (‘R018′,’NG SIN YIN’,’530602-07-5176′,’F’,’61’,’09100′,’BALING’,’3/2/2013′,’50001′,’NULL’,’NULL’);
    INSERT INTO RESIDENT VALUES (‘R019′,’OMAR BIN HASAN’,’450311-03-5405′,’M’,’69’,’14000′,’BUKIT MERTAJAM’,’15/5/2013′,’20002′,’NULL’,’810123-03-6210′);
    INSERT INTO RESIDENT VALUES (‘R020′,’PUVANESWARI A/P MALAYANDY’,’540312-09-5244′,’F’,’60’,’13210′,’KEPALA BATAS’,’16/2/2013′,’40002′,’NULL’,’NULL’);
    INSERT INTO RESIDENT VALUES (‘R021′,’RAMLAH BINTI MD NOR’,’501018-01-5268′,’F’,’64’,’09010′,’KULIM’,’18/3/2013′,’50002′,’NULL’,’810725-01-5067′);
    INSERT INTO RESIDENT VALUES (‘R022′,’ROHANI ABDULLAH’,’550708-01-5788′,’F’,’59’,’02607′,’ARAU’,’31/12/2012′,’40002′,’NULL’,’690422-01-6261′);
    INSERT INTO RESIDENT VALUES (‘R023′,’SAADIAH BINTI ROSDI’,’510302-02-6186′,’F’,’63’,’11960′,’BATU MAUNG’,’31/12/2012′,’30001′,’NULL’,’NULL’);
    INSERT INTO RESIDENT VALUES (‘R024′,’SENAPI BIN ABDULLAH’,’510117-03-5039′,’M’,’63’,’13220′,’PINANG TUNGGAL’,’26/2/2013′,’50002′,’NULL’,’760201-03-5867′);
    INSERT INTO RESIDENT VALUES (‘R025′,’SITI FATIMAH BINTI RAHMAN’,’530121-08-5004′,’F’,’61’,’09010′,’KULIM’,’28/3/2013′,’20001′,’NULL’,’781208-08-5537′);
    INSERT INTO RESIDENT VALUES (‘R026′,’SUMANT SUBRAMANIAM’,’440720-06-6117′,’M’,’70’,’09010′,’KULIM’,’28/5/2013′,’60002′,’NULL’,’NULL’);
    INSERT INTO RESIDENT VALUES (‘R027′,’TAN CHAI HOON’,’440420-06-5841′,’M’,’70’,’13300′,’TASEK GELUGOR’,’5/6/2013′,’60001′,’NULL’,’751126-06-5303′);
    INSERT INTO RESIDENT VALUES (‘R028′,’TAN SOON WEI’,’451231-08-5505′,’M’,’69’,’13700′,’PERAI’,’24/10/2013′,’60001′,’NULL’,’NULL’);
    INSERT INTO RESIDENT VALUES (‘R029′,’VALARMATHY A/P MUNISAMY’,’520923-07-5148′,’F’,’62’,’09100′,’BALING’,’7/9/2013′,’40002′,’NULL’,’NULL’);
    INSERT INTO RESIDENT VALUES (‘R030′,’WONG JIN YIN’,’480619-02-6578′,’F’,’66’,’02500′,’KANGAR’,’27/12/2012′,’40003′,’NULL’,’NULL’);

    /*Insert GUARDIAN Rows*/
    INSERT INTO GUARDIAN
    VALUES(‘HADI BIN FAUZI’,’770805-06-5821′,’LOT 201,KG BADAK’,’019-7611064′);

    INSERT INTO GUARDIAN
    VALUES(‘AHMAD KAMAL BIN OMAR’,’810123-03-6210′,’NO 12,TAMAN CATUR’,’019-3342106′);

    INSERT INTO GUARDIAN
    VALUES(‘NAZRI BIN JUSOH’,’781115-07-5617′,’NO 28,TAMAN SAWI’,’016-2454699′);

    INSERT INTO GUARDIAN
    VALUES(‘AFIF BIN ISHAK’,’781208-08-5537′,’N0 15,TAMAN DESA AMAN’,’017-2675810′);

    INSERT INTO GUARDIAN
    VALUES(‘AZIZUL BIN AZIZ’,’710815-02-6065′,’LOT160, KG PADANG ENGGANG’,’019-4807721′);

    INSERT INTO GUARDIAN
    VALUES(‘SHAHRIN BIN KAMARUDDIN’,’850301-01-5537′,’NO 1,TAMAN SRI BAYU’,’012-6569339′);

    INSERT INTO GUARDIAN
    VALUES(‘HASBULLAH BIN SENAPI’,’760201-03-5867′,’NO 811,TAMAN SEMARAK’,’016-2347654′);

    INSERT INTO GUARDIAN
    VALUES(‘HAIKAL BIN NAZRI’,’850909-02-5209′,’NO 298C , BANDAR SERI ASTANA’,’017-2541100′);

    INSERT INTO GUARDIAN
    VALUES(‘KAMELIA BINTI MAHMUD’,’810123-07-6210′,’NO 12 TAMAN KEMPAS’,’017-4218892′);

    INSERT INTO GUARDIAN
    VALUES(‘TAN YEN CERK’,’751126-06-5303′,’NO 49 TAMAN SETIA 1′,’019-2389876′);

    INSERT INTO GUARDIAN
    VALUES(‘LIM KEN GUAN’,’871030-07-5313′,’LOT 891 KG MASJID DARAT’,’012-4586097′);

    INSERT INTO GUARDIAN
    VALUES(‘YUSOF BIN HARUN’,’770913-02-6023′,’NO 23 KG TANJONG API’,’013-4211042′);

    INSERT INTO GUARDIAN
    VALUES(‘HAYATI BINTI IDRIS’,’760914-08-5316′,’NO 106 KG BATU LAUT’,’019-2144210′);

    INSERT INTO GUARDIAN
    VALUES(‘ROSLAN BIN HAMID’,’731203-02-5021′,’473, TAMAN ALOR MENGKUDU’,’017-3070449′);

    INSERT INTO GUARDIAN
    VALUES(‘MARINI BINTI YAAKOB’,’781224-09-5468′,’164, TAMAN SULTANAH ‘,’017-2643867’);

    INSERT INTO GUARDIAN
    VALUES(‘LATIFAH BINTI OSMAN’,’860622-08-5012′,’NO 1588 TAMAN MAWAR’,’019-5960806′);

    INSERT INTO GUARDIAN
    VALUES(‘ANUAR BIN ISMAIL’,’810725-01-5067′,’137, TAMAN KELISA 2′,’013-2411334′);

    INSERT INTO GUARDIAN
    VALUES(‘ZAHARI BIN YAHYA’,’690422-01-6261′,’NO366 TAMAN TABUNG HAJI’,’012-2141510′);

    INSERT INTO GUARDIAN
    VALUES(‘ISMADI BIN SENAWI’,’780121-02-5771′,’NO 61 TAMAN DESA PERMAI 3′,’013-3908709′);

    INSERT INTO GUARDIAN
    VALUES(‘HAFIZ BIN HASHIM’,’830222-02-5175′,’NO 50A, BANDAR PERDANA SEKSYEN 2′,’012-4554526′);

    /*Insert NEEDS Rows*/
    INSERT INTO NEEDS VALUES(‘33001′,’HEALTH CARE’);
    INSERT INTO NEEDS VALUES(‘33002′,’MEALS’);
    INSERT INTO NEEDS VALUES(‘33003′,’PAMPERS’);
    INSERT INTO NEEDS VALUES(‘33004′,’GROCERIES’);
    INSERT INTO NEEDS VALUES(‘33005′,’BOOKS’);
    INSERT INTO NEEDS VALUES(‘33006′,’CRAFT MATERIALS’);

    /*Insert ACTIVITY Rows*/
    INSERT INTO ACTIVITY VALUES(‘22001′,’MUSICAL EVENTS’);
    INSERT INTO ACTIVITY VALUES(‘22002′,’SEWING ACTIVITIES’);
    INSERT INTO ACTIVITY VALUES(‘22003′,’COOKING’);
    INSERT INTO ACTIVITY VALUES(‘22004′,’BIRTHDAY PARTY’);
    INSERT INTO ACTIVITY VALUES(‘22005′,’TALK SERIES’);
    INSERT INTO ACTIVITY VALUES (‘22006′,’VACATION’);

    /*Insert EXPENSES Rows*/
    INSERT INTO EXPENSES VALUES(‘11001′,’FOODS’,’22-Mar-2014′,’33001′,’22001′);
    INSERT INTO EXPENSES VALUES(‘11002′,’CLOTHS’,’27-Mar-2014′,’33002′,’22002′);
    INSERT INTO EXPENSES VALUES(‘11003′,’MEDICINE’,’22-Apr-2014′,’33003′,’22003′);
    INSERT INTO EXPENSES VALUES(‘11004′,’TOILETRIES’,’11-May-2014′,’33004′,’22004′);
    INSERT INTO EXPENSES VALUES(‘11005′,’ROOM’,’29-Aug-2014′,’33005′,’22005′);
    INSERT INTO EXPENSES VALUES(‘11006′,’TRAVEL EXPENSES’,’02-Oct-2014′,’33006′,’22006′);

    /* Insert PRODUCT Rows*/
    INSERT INTO PRODUCT VALUES(‘20001’, ‘SMALL BASKET’, ‘20.00’, ‘30011’);

    INSERT INTO PRODUCT VALUES(‘20002’, ‘MEDIUM BASKET’, ‘30.00’, ‘30011’);

    INSERT INTO PRODUCT VALUES(‘20003’, ‘LARGE BASKET’, ‘45.00’, ‘30011’);

    INSERT INTO PRODUCT VALUES(‘30001’, ‘MAT’, ‘35.00’, ‘40011’);

    INSERT INTO PRODUCT VALUES(‘40001’, ‘FLOWERY BOX’, ‘10.00’, ‘50011’);

    INSERT INTO PRODUCT VALUES(‘40002’, ‘PLAIN BOX’, ‘5.00’, ‘50011’);

    INSERT INTO PRODUCT VALUES(‘40003’, ‘RIBBON BOX’, ‘10.00’, ‘50011’);

    INSERT INTO PRODUCT VALUES(‘50001’, ‘SMALL BAG’, ‘15.00’, ‘60011’);

    INSERT INTO PRODUCT VALUES(‘50002’, ‘BIG BAG’, ‘30.00’, ‘60011’);

    INSERT INTO PRODUCT VALUES(‘60001’, ‘FOOD COVER M’, ‘25.00’, ‘70011’);

    INSERT INTO PRODUCT VALUES(‘60002’, ‘FOOD COVER L’, ‘30.00’, ‘70011’);

    /*Insert SALE Rows*/
    INSERT INTO SALE VALUES(‘30011’, ‘180.00’, ‘8’, ‘99002’);

    INSERT INTO SALE VALUES(‘40011’, ‘350.00’, ’10’, ‘99002’);

    INSERT INTO SALE VALUES(‘50011’, ‘78.50’, ’65’, ‘99002’);

    INSERT INTO SALE VALUES(‘60011’, ‘225.00’, ’10’, ‘99002’);

    INSERT INTO SALE VALUES(‘70011’, ‘950.00’, ’35’, ‘99002’);

    /*Insert DONATION Rows*/
    INSERT INTO DONATION VALUES(‘11110’, ‘NGO’, ’12/09/2014′, ‘99001’);

    INSERT INTO DONATION VALUES(‘22223’, ‘NGO’, ’22/09/2014′, ‘99001’);

    INSERT INTO DONATION VALUES(‘11112’, ‘PERSONAL’, ’22/09/2014′, ‘99001’);

    INSERT INTO DONATION VALUES(‘22221’, ‘NGO’, ’10/10/2014′, ‘99001’);

    INSERT INTO DONATION VALUES(‘22222’, ‘PERSONAL’, ’30/10/2014′, ‘99001’);

    /*Insert INCOME Rows*/
    INSERT INTO INCOME VALUES (‘99002’, ‘60011’, ‘NULL’, ‘10003’);

    INSERT INTO INCOME VALUES(‘99002’, ‘30011’, ‘NULL’, ‘10001’);

    INSERT INTO INCOME VALUES(‘99001’, ‘NULL’, ‘11112’, ‘10006’);

    INSERT INTO INCOME VALUES(‘99001’, ‘NULL’, ‘22221’, ‘10004’);

    INSERT INTO INCOME VALUES(‘99002’, ‘70011’, ‘NULL’, ‘10005’);

    }

    can you check for me whether it is correct or not?

  13. 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
  14. 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
  15. 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.

  16. 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
  17. 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
  18. 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.

        1. thatjeffsmith Post
          Author
          1. 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

          2. thatjeffsmith Post
            Author
    1. thatjeffsmith Post
      Author
      1. 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.

  19. 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
  20. 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
  21. 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
      1. 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.

        1. thatjeffsmith Post
          Author
          1. 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 ?

          2. 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.

  22. 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
      1. 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?

        1. 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.

  23. 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?

  24. 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.

      1. 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.

        1. 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.

          1. 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.

        2. 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 🙂

  25. 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.

  26. 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
  27. 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.

  28. thatjeffsmith Post
    Author
  29. 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 *