{"id":2696,"date":"2023-02-28T08:00:04","date_gmt":"2023-02-28T13:00:04","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/?p=2696"},"modified":"2023-03-15T09:47:01","modified_gmt":"2023-03-15T14:47:01","slug":"convert-sql-into-e-r-diagram-with-sqlflow","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2023\/02\/convert-sql-into-e-r-diagram-with-sqlflow\/","title":{"rendered":"Convert SQL into E-R Diagram with SQLFlow"},"content":{"rendered":"\n

SQLFlow<\/a> is capable to convert SQL to Entity-Relation(ER) Diagram<\/strong> as well as to visualize the relations between tables and fields so that you can quickly understand the design model of the database and conduct efficient team communication.<\/p>\n\n\n\n

## Two Sources of Visualization ER Model<\/a><\/h2>\n\n\n\n

- From SQL Script<\/strong><\/li><\/ul>\n\n\n\n

  SQLFlow can analyze Database creation SQL script and visualize the provided scripts. In order to create ER diagram, user need to provide SQL statements such as `create table<\/code> or `alter table<\/code> and include foreign keys.<\/p>\n\n\n\n``

  - From Database<\/strong><\/li><\/ul>\n\n\n\n

    You can simply make SQLFlow connect to your database for the ER diagram. SQLFlow will automatically retrieve metadata from the database and generate the ER diagram.<\/p>\n\n\n\n

    ## Three Ways to Visualize ER Model<\/h2>\n\n\n\n

    Let’s now take a look on how exactly to use Gudu SQLFlow<\/a>.<\/p>\n\n\n\n

    Gudu SQLFlow<\/a> provides three ways:<\/p>\n\n\n\n

    #### 1. Paste SQL Statements into the SQLText Editor<\/a><\/h4>\n\n\n\n

    Directly paste your SQL statements into the SQLText Editor<\/p>\n\n\n\n

    <\/figure>\n\n\n\n

    Click the icon of the ER model and you will have the correspond ER diagram instantly.<\/p>\n\n\n\n

    <\/figure>\n\n\n\n

    #### 2. Upload SQL files<\/h4>\n\n\n\n

    Click `upload sql<\/code><\/p>\n\n\n\n`

    <\/figure>\n\n\n\n

    Choose `upload file<\/code> as the job source and choose the SQL type in the `dbvendor<\/code>. Upload file and create the Job.<\/p>\n\n\n\n``

    <\/figure>\n\n\n\n

    Choose the job in the job list panel when it is complete.<\/p>\n\n\n\n

    <\/figure>\n\n\n\n

    Click `show ER diagram<\/code> in the schema explorer and check the result.<\/p>\n\n\n\n`

    <\/figure>\n\n\n\n

    #### 3. Connect to DB<\/h4>\n\n\n\n

    Connect to your database for the ER diagram, check following table for the supported database types and the permissions\/roles needed:<\/p>\n\n\n\n

    Database Type<\/strong><\/td> | Permissions\/Roles required<\/strong><\/td> | Comment<\/strong><\/td><\/tr>
    MySQL<\/td> | SELECT<\/td> | ER Diagram needs DB user to have SELECT permission<\/td><\/tr>
    PostgreSQL<\/td> | SELECT<\/td> | ER Diagram needs DB user to have SELECT permission<\/td><\/tr>
    SQL Server<\/td> | SELECT<\/td> | ER Diagram needs DB user to have SELECT permission<\/td><\/tr>
    Oracle<\/td> | DBA<\/td> | To generate ER Diagram from Oracle database, input user must has DBA role , otherwise `ORA-01031<\/code> will be returned.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Hint: Other databases are not in current version’s support list and will be supported in our future release<\/em><\/p>\n\n\n\n <\/figure>\n\n\n\n Choose the job in the job list panel when it is complete.<\/p>\n\n\n\n <\/figure>\n\n\n\n Click `show ER diagram<\/code> in the schema explorer and check the result.<\/p>\n\n\n\n` <\/figure>\n\n\n\n ## E-R Diagram<\/h2>\n\n\n\n The following capture is the ER diagram of Northwind database<\/a> generated by Gudu SQLFlow<\/a>. We will have tables, fields, field types and different kinds of key which are under different icons in the diagram.<\/p>\n\n\n\n <\/figure>\n\n\n\n The following ER diagram is a simple help-desk tickets system<\/a> generate by Gudu SQLFlow<\/a> from the SQL file<\/a>.<\/p>\n\n\n\n <\/figure>\n\n\n\n ## SQL Requirements to Create ER Diagram<\/h2>\n\n\n\n In order to create ER diagram, foreign keys should be included in the provided SQL files. Other keys such as primary key, index key or unique key are not mandatory.<\/p>\n\n\n\n #### A. Foreign key<\/h4>\n\n\n\n Foreign key is to build ER relations between different tables. Statements such as `create table<\/code> and `alter table<\/code> usually contain this information.<\/p>\n\n\n\n`` 1. Foreign key in create table<\/strong><\/p>\n\n\n\n

    ####

        CREATE TABLE SCOTT.EMP \n   (\t\n\tEMPNO NUMBER(4,0), \n\tENAME VARCHAR2(10), \n\tDEPTNO NUMBER(2,0), \n\tCONSTRAINT PK_EMP PRIMARY KEY (EMPNO),\n\tFOREIGN KEY(DEPTNO) REFERENCES SCOTT.DEPT(DEPTNO)\n   );<\/code><\/pre>\n\n\n\n2. Foreign key in alter table<\/strong><\/p>\n\n\n\nALTER TABLE SCOTT.EMP ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (\"DEPTNO\")\n\t  REFERENCES SCOTT.DEPT (\"DEPTNO\") ENABLE;<\/code><\/pre>\n\n\n\nB. Primary key, index key, unique key<\/h4>\n\n\n\n1. Primary key<\/a><\/strong><\/p>\n\n\n\nPrimary key can be specified in create table<\/code> and alter table<\/code> statement.<\/p>\n\n\n\nCREATE TABLE SCOTT.EMP \n   (\t\n\tEMPNO NUMBER(4,0), \n\tENAME VARCHAR2(10), \n\tDEPTNO NUMBER(2,0), \n\tCONSTRAINT PK_EMP PRIMARY KEY (EMPNO),\n\tFOREIGN KEY(DEPTNO) REFERENCES SCOTT.DEPT(DEPTNO)\n   );<\/code><\/pre>\n\n\n\nALTER TABLE SCOTT.DEPT\nADD CONSTRAINT dept_pk PRIMARY KEY (DEPTNO);<\/code><\/pre>\n\n\n\nIcon of primary key in ER diagram<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\n2. Unique key<\/strong><\/p>\n\n\n\nUnique key can be specified in create table<\/code> and alter table<\/code> statement.<\/p>\n\n\n\nCREATE TABLE SCOTT.DEPT \n   (\n    DEPTNO NUMBER(2,0), \n\tDNAME VARCHAR2(14), \n\tLOC VARCHAR2(13),\n\tCONSTRAINT constraint_name UNIQUE (DEPTNO)\n   ) ;<\/code><\/pre>\n\n\n\nALTER TABLE SCOTT.DEPT \nADD CONSTRAINT constraint_name UNIQUE (DEPTNO);  <\/code><\/pre>\n\n\n\nIcon of unique key in ER diagram<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\n3. Index key<\/strong><\/p>\n\n\n\nIndex key can be specified using create index<\/code> statement.<\/p>\n\n\n\n-- Oracle\nCREATE INDEX SCOTT.DEPT_INDEX \nON SCOTT.DEPT (LOC);<\/code><\/pre>\n\n\n\nOr alter table add index<\/p>\n\n\n\n-- MySQL\nALTER TABLE\n    `ticketit` ADD INDEX `ticketit_subject_index`(`subject`);<\/code><\/pre>\n\n\n\nIcon of index key in ER diagram<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"SQLFlow is capable to convert SQL to Entity-Relation(ER) Diagram as well as to visualize the relations between tables and fields so that you can quickly understand the design model of the database and conduct efficient team communication. Two Sources of Visualization ER Model From SQL Script SQLFlow can analyze Database creation SQL script and visualize the provided scripts. In order to create ER diagram, user need to provide SQL statements such as create table or alter table and include foreign keys. From Database You can simply make SQLFlow connect to your database for the ER diagram. SQLFlow will automatically retrieve\u2026<\/p>\n","protected":false},"author":6,"featured_media":2710,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[66,93],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nConvert SQL into E-R Diagram with SQLFlow<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n\n\n\n\t\n\t\n\t\n
    `