Gudu SQLFlow Product Docs
  • 1. Introduction
    • What is Gudu SQLFlow?
      • What SQLFlow can do
      • Architecture Overview
    • Getting Started
      • Sign up a new account
        • Team Management
        • Delete My Account
        • Activate by entering a coupon
      • How to use SQLFlow
      • Different modes in Gudu SQLFlow
        • Query mode
        • Job mode
      • Basic Usage
      • Convert SQL to E-R Diagram
      • Colors in SQLFlow Diagram
      • Show call relationship
    • Installation
      • Version and Users
        • Cloud and On-Premise version
        • SQLFlow before Version 6
          • For older version SQLFlow under Linux
          • For older version SQLFlow under MacOS
          • For older version SQLFlow under Windows
      • Linux
      • MacOS
      • Windows
      • Docker
      • Clickhouse Installation
        • Clickhouse For CentOs
        • Clickhouse For Ubuntu/Debian/RHEL
      • Troubleshooting
      • Upgrade
      • Third Party Components
      • Renew License File
    • UI
      • SQLText Editor
      • Schema Explorer
      • Diagram Panel
      • Settings
      • Job Management
        • Job Sources
    • Dlineage Tool
      • Overview
      • Usage
        • Analyze data linege from SQL files
        • Analyze data linege from a database
        • Resolve the ambiguous columns in SQL query
        • Map the DataFlowAnalyzer and the settings on SQLFlow UI
        • Settings
      • Dataflow.xml structure
      • FAQ
  • 2. CONCEPTS
    • Data Lineage Basics
      • Dataflow
        • Relations generated by SQLFlow
      • Direct Dataflow
      • Indirect Dataflow
      • Aggregate function and Dataflow
      • Dataflow chain
    • Data Lineage Format Reference
  • 3. API Docs
    • Prerequisites
    • Using the Rest API
    • SQLFlow Rest API reference
      • User Interface
      • Generation Interface
        • /sqlflow
        • /sqlflow/selectedgraph/table_level_lineage
        • /sqlflow/selectedgraph/image
        • /sqlflow/graph
        • /sqlflow/graph/table_level_lineage
        • /sqlflow/graph/image
        • /sqlflow/downstreamGraph
        • /sqlflow/upstreamGraph
        • /sqlflow/erdiagramSelectGraph
        • /sqlflow/leftMostSourceTableGraph
      • Job Interface
        • /submitUserJob
        • /displayUserJobSummary
        • /displayUserJobsSummary
        • /exportLineageAsJson
        • /exportFullLineageAsJson
        • /exportLineageAsGraphml
        • /submitPersistJob
        • /displayUserLatestJobTableLevelLineage
      • Export Image
      • Export CSV
        • /sqlflow/exportFullLineageAsCsv
        • /job/exportFullLineageAsCsv
    • Swagger UI
    • Export the data lineage result
    • Python
      • Basic Usage
      • Advanced Usage
    • SQL Parser API
      • checkSyntax
  • 4. SQLFlow Widget
    • Widget Get started
    • Usages
    • Widget API Reference
  • 5. Databases
    • Database Objects
      • Azure
      • DB2
  • 6. SQLFlow-ingester
    • Introduction
      • SQLFlow-Exporter
      • SQLFlow-Extractor
      • SQLFlow-Submitter
    • Get Started
      • SQL Server
    • SQLFlow-Ingester Java API Usage
    • Understand the format of exported data
      • Oracle
      • Microsoft SQL Server
      • MySQL
      • PostgreSQL
    • List of Supported dbVendors
    • Git Repo
    • Third Party Components
  • 7. Reference
    • Lineage Model
      • Json Format Lineage Model
      • XML Format Lineage Model
      • Data Lineage Elements
    • Database Model
  • 8. other
    • FAQ
      • Handling Internal Database
      • Delete Your Account
      • Table Form Data Without Intermediates
      • Not all schema exported from Oracle
      • Lineage Customization
    • Roadmap
    • SQL Samples
      • Exchange table partition
      • Generate relationship for renamed table
      • Snowflake table function lineage detection
    • Change Logs
    • SQLFlow with Oracle XML functions
    • Major Organizations Utilizing SQLFlow
Powered by GitBook
On this page
  • Two Sources of Visualization ER Model
  • Three Ways to Visualize ER Model
  • 1. Paste SQL Statements into the SQLText Editor
  • 2. Upload SQL files
  • 3. Connect to DB
  • E-R Diagram
  • SQL Requirements to Create ER Diagram
  • A) Foreign key
  1. 1. Introduction
  2. Getting Started

Convert SQL to E-R Diagram

https://blog.sqlflow.cn/gudu-sqlflow-er-diagram/

PreviousBasic UsageNextColors in SQLFlow Diagram

Last updated 1 year ago

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 metadata from the database and generate the ER diagram.

Hint: The feature is only available in Simple Job. E-R Diagram visualization is currently not supported in Regular Job.

Three Ways to Visualize ER Model

Let's now take a look on how exactly to use .

provides three ways:

1. Paste SQL Statements into the SQLText Editor

Directly paste your SQL statements into the SQLText Editor

Click the icon of the ER model and you will have the correspond ER diagram instantly.

2. Upload SQL files

Click upload sql

Choose the job in the job list panel when it is complete.

Click show ER diagram in the schema explorer and check the result.

3. Connect to DB

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

Database Type
Permissions/Roles required
Comment

MySQL

SELECT

ER Diagram needs DB user to have SELECT permission

PostgreSQL

SELECT

ER Diagram needs DB user to have SELECT permission

SQL Server

SELECT

ER Diagram needs DB user to have SELECT permission

Oracle

DBA

To generate ER Diagram from Oracle database, input user must has DBA role , otherwise ORA-01031 will be returned.

Hint: Other databases are not in current version's support list and will be supported in our future release

Choose the job in the job list panel when it is complete.

Click show ER diagram in the schema explorer and check the result.

E-R Diagram

SQL Requirements to Create ER Diagram

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.

A) Foreign key

Foreign key is to build ER relations between different tables. Statements such as create table and alter table usually contain this information.

1. Foreign key in create table

CREATE TABLE SCOTT.EMP 
   (	
	EMPNO NUMBER(4,0), 
	ENAME VARCHAR2(10), 
	DEPTNO NUMBER(2,0), 
	CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
	FOREIGN KEY(DEPTNO) REFERENCES SCOTT.DEPT(DEPTNO)
   );

2. Foreign key in alter table

ALTER TABLE SCOTT.EMP ADD CONSTRAINT FK_DEPTNO FOREIGN KEY ("DEPTNO")
	  REFERENCES SCOTT.DEPT ("DEPTNO") ENABLE;

B) Primary key, index key, unique key

1. Primary key

Primary key can be specified in create table and alter table statement.

CREATE TABLE SCOTT.EMP 
   (	
	EMPNO NUMBER(4,0), 
	ENAME VARCHAR2(10), 
	DEPTNO NUMBER(2,0), 
	CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
	FOREIGN KEY(DEPTNO) REFERENCES SCOTT.DEPT(DEPTNO)
   );
ALTER TABLE SCOTT.DEPT
ADD CONSTRAINT dept_pk PRIMARY KEY (DEPTNO);

Icon of primary key in ER diagram

2. Unique key

Unique key can be specified in create table and alter table statement.

CREATE TABLE SCOTT.DEPT 
   (
    DEPTNO NUMBER(2,0), 
	DNAME VARCHAR2(14), 
	LOC VARCHAR2(13),
	CONSTRAINT constraint_name UNIQUE (DEPTNO)
   ) ;
ALTER TABLE SCOTT.DEPT 
ADD CONSTRAINT constraint_name UNIQUE (DEPTNO);  

Icon of unique key in ER diagram

3. Index key

Index key can be specified using create index statement.

-- Oracle
CREATE INDEX SCOTT.DEPT_INDEX 
ON SCOTT.DEPT (LOC);

Or alter table add index

-- MySQL
ALTER TABLE
    `ticketit` ADD INDEX `ticketit_subject_index`(`subject`);

Icon of index key in ER diagram

Choose upload file as the job source and choose the SQL type in the dbvendor. Upload file and create the .

The following capture is the ER diagram of generated by . We will have tables, fields, field types and different kinds of key which are under different icons in the diagram.

The following ER diagram is a generate by from .

Job
Northwind database
Gudu SQLFlow
simple help-desk tickets system
Gudu SQLFlow
SQL file
Gudu SQLFlow
Gudu SQLFlow
Gudu SQLFlow