ER Diagram SQL: From CREATE TABLE to Diagram and Back
November 11, 2026 · 11 min read
ER Diagram SQL Guide: From CREATE TABLE to Diagram and Back
The ER diagram SQL round trip follows a few fixed rules: every table becomes an entity, every column an attribute, and every foreign key a relationship line. This guide reads a sample CREATE TABLE schema, draws its diagram by hand in Mermaid or automatically in your database tool, then turns a diagram back into DDL.
What an ER Diagram Shows About Your SQL Schema
An entity-relationship diagram is a picture of your tables and the foreign keys between them. Boxes are tables, rows inside the boxes are columns, and lines are foreign keys. The symbols at each end of a line say how many rows can sit on that side of the relationship.
Mermaid and most modelling tools draw crow's foot notation, where a three-pronged foot means "many". Textbook pages often show Chen notation, with ovals and diamonds. That style suits early conceptual modelling, but it doesn't map onto columns and constraints, so this guide sticks to crow's foot. Our ER diagram symbols reference shows both notations side by side.
The useful part is that a schema already contains everything the diagram needs. You don't invent relationships; you read them from PRIMARY KEY, FOREIGN KEY, UNIQUE and NOT NULL.
The Example Schema: Five CREATE TABLE Statements
Here's a small shop schema. It uses plain types and table-level FOREIGN KEY clauses, so it runs on PostgreSQL, MySQL and SQLite without edits. SQLite parses the foreign keys, but enforcement is off by default until you run PRAGMA foreign_keys = ON. The diagram is the same either way.
CREATE TABLE customers (
id integer PRIMARY KEY,
email varchar(255) NOT NULL UNIQUE,
name varchar(100) NOT NULL
);
CREATE TABLE coupons (
id integer PRIMARY KEY,
code varchar(20) NOT NULL UNIQUE,
percent_off integer NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL,
coupon_id integer,
ordered_on date NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (id),
FOREIGN KEY (coupon_id) REFERENCES coupons (id)
);
CREATE TABLE products (
id integer PRIMARY KEY,
sku varchar(40) NOT NULL UNIQUE,
name varchar(200) NOT NULL,
price numeric(10,2) NOT NULL
);
CREATE TABLE order_items (
order_id integer NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders (id),
FOREIGN KEY (product_id) REFERENCES products (id)
);
Each constraint carries meaning for the picture. orders.customer_id is NOT NULL, so every order belongs to exactly one customer. orders.coupon_id allows NULL, so an order may have no coupon. The composite key on order_items makes it a join table between orders and products.
How Do You Get an ER Diagram From SQL? Map Each Constraint
To get an ER diagram from SQL, walk through each CREATE TABLE and translate one construct at a time. The table below covers every case in the example schema, plus one-to-one, using Mermaid's markers in the last column.
| SQL construct | Diagram element | Mermaid |
|---|---|---|
CREATE TABLE orders | Entity box | an orders block |
Column ordered_on date | Attribute row | date ordered_on |
PRIMARY KEY | Key attribute | PK |
UNIQUE column | Alternate key | UK |
FOREIGN KEY ... REFERENCES | Relationship line, FK on the child | FK plus a line |
FK column NOT NULL | Exactly one on the parent end | || |
| FK column nullable | Zero or one on the parent end | |o |
FK column also UNIQUE | One-to-one | ||..o| |
| FK is part of the PK | Identifying relationship | solid -- |
| FK outside the PK | Non-identifying relationship | dashed .. |
| Table whose PK is two FKs | Join table for many-to-many | two lines into it |
The child end needs one caution. SQL can stop an order from pointing at a missing customer, but it can't force a customer to have at least one order. So the child end is almost always o{ (zero or more). Use |{ (one or more) only when a business rule enforced in your application says so, and note where that rule lives.
Writing a Mermaid erDiagram by Hand From CREATE TABLE
Mermaid doesn't read SQL, so this step is manual translation. Write the relationship lines first, one per foreign key, then the entity blocks. Here's the example schema as an erDiagram, followed by the diagram it produces:
```mermaid
erDiagram
customers ||..o{ orders : places
coupons |o..o{ orders : discounts
orders ||--o{ order_items : contains
products ||--o{ order_items : "appears in"
customers {
integer id PK
varchar(255) email UK
}
coupons {
integer id PK
varchar(20) code UK
}
orders {
integer id PK
integer customer_id FK
integer coupon_id FK "nullable"
date ordered_on
}
order_items {
integer order_id PK, FK
integer product_id PK, FK
integer quantity
}
products {
integer id PK
varchar(40) sku UK
}
```
This renders five boxes. A dashed line joins customers to orders, with two bars on the customer end and a circle plus crow's foot (zero or more) on the order end. Coupons connect with a circle and bar, meaning zero or one. The two lines into order_items are solid because both foreign keys sit inside its primary key.
We prefer keeping only keys and the columns that explain a relationship in the diagram. Names, prices and timestamps add noise, and the DDL stays the full source of truth. Mermaid's entity relationship syntax reference lists every marker and attribute rule if you need more than the translation above. For fence basics, see our guide to Mermaid diagrams in Markdown. Our Mermaid ER diagram syntax guide builds a schema step by step, from entities to keys.
Try a cut-down version below. Change |o to || on the coupon line and the circle on that end becomes a bar.
Pulling the DDL Out of an Existing Database
If the database already exists, export its schema before you draw anything. Each engine has a one-line way to print the CREATE statements without the data:
- PostgreSQL:
pg_dump --schema-only -d shop -f shop_schema.sqldumps object definitions only. Add-t ordersto limit it to one table, per the pg_dump documentation. - MySQL: run
SHOW CREATE TABLE orders;for each table you care about. - SQLite:
sqlite3 shop.db ".schema"prints every CREATE statement in the file. - SQL Server: in SSMS Object Explorer, right-click a table, then choose Script Table as, CREATE To, New Query Editor Window. For many tables, Tasks, Generate Scripts with schema only does the same in bulk.
Don't paste all 60 tables of a production schema into one diagram. Nobody can read it. Split by area (billing, catalog, users) and draw each area with the few tables it touches.
Generating an SQL ER Diagram in DBeaver, Workbench or SSMS
You can't draw an ER diagram in SQL itself, because the language has no diagram syntax. If you'd rather not translate by hand, the database tool you already use can draw an SQL ER diagram from a live connection.
DBeaver: open a table and switch to its Diagram tab, or double-click a schema to see all its tables. You can export the diagram as PNG, GIF, BMP or GraphML; SVG export is listed as PRO-only in DBeaver's docs.
MySQL Workbench: choose Database, then Reverse Engineer, and pick the schemas to import. With the default "place imported objects on a diagram" option, Workbench builds an EER diagram from them.
SSMS: in Object Explorer, right-click the Database Diagrams folder, choose New Database Diagram and add tables. Microsoft's database diagram docs note that each diagram is stored in the database itself.
Online generators that import DDL also exist. Whichever you use, expect to prune and rearrange: an auto-generated diagram of a real schema is rarely readable until you remove audit columns and lookup tables. Our comparison of free ER diagram tools shows which ones accept pasted DDL.
How Do You Turn an ER Diagram Into SQL?
Going from an ER diagram to SQL reverses the same rules. Each entity becomes a CREATE TABLE. In a one-to-many relationship the foreign key column goes on the "many" side, and an optional end on the parent means that column is nullable. In a one-to-one, put the foreign key on the optional side and add UNIQUE to it.
A many-to-many line can't become a single foreign key. Take this one-line diagram:
```mermaid
erDiagram
authors }o..o{ books : writes
```
It turns into three tables, with a join table holding both keys:
CREATE TABLE authors (
id integer PRIMARY KEY,
name varchar(100) NOT NULL
);
CREATE TABLE books (
id integer PRIMARY KEY,
title varchar(200) NOT NULL
);
CREATE TABLE book_authors (
author_id integer NOT NULL,
book_id integer NOT NULL,
PRIMARY KEY (author_id, book_id),
FOREIGN KEY (author_id) REFERENCES authors (id),
FOREIGN KEY (book_id) REFERENCES books (id)
);
Line style tells you where the key goes. A solid identifying line means the foreign key belongs inside the child's primary key, as in book_authors. A dashed non-identifying line means a plain foreign key column next to the child's own id.
Modelling tools can do this step from their own models. DBeaver has a Generate SQL action for selected tables, and Workbench's Database, Forward Engineer wizard shows the script for review before running it. Nothing turns a Mermaid diagram into DDL for you unless you script that yourself.
ER Diagram SQL Workflow: Keeping Diagram and Schema in Sync
A diagram that drifts from the schema is worse than none. Store the erDiagram block in README.md or docs/schema.md, next to your migrations, inside a fenced code block (our code block guide covers fences). GitHub renders Mermaid in Markdown files, issues, pull requests, discussions and wikis.
Staying in sync is a habit: change the diagram in the same pull request as the migration. Reviewers see both diffs side by side, and a missing diagram update stands out. For a quick review check, count the foreign keys in the DDL (FOREIGN KEY clauses plus inline REFERENCES) and the relationship lines in the diagram. The numbers should match, minus any tables you left out on purpose.
Some slides and wikis don't render Mermaid. For those, paste the lines inside the fence into our Mermaid editor, which runs in your browser and exports PNG or SVG.
The ER diagram SQL workflow comes down to reading constraints carefully. Primary keys become key attributes, foreign keys become lines, and nullability decides the parent end. Draw the diagram once from your DDL, keep it beside your migrations, and update both in the same pull request. To check an erDiagram before you commit it, paste the fenced block into the editor and watch the preview render it on the right.