MySQL ER Diagram, Reverse Engineer and Design Schemas Visually
For developers and DBAs who need a diagram of the tables and foreign keys in a MySQL database, or who are designing new MySQL tables.
On this page
DbSchema, an ER diagram tool, connects to MySQL and draws a diagram of the database: a box for each table with its columns, and a line for each foreign key. In the free Community Edition:
- On the Start New tab of the Welcome Screen, choose Connect to Database.
- Click the MySQL tile, choose New Connection on the right, and press Connect.
- In the connection dialog, set Method to Standard. Type the Database User, the Password, and the database name in Database.
- Press Test Connection, then Connect. The first time, DbSchema downloads the MySQL JDBC driver.
- In the Reverse Engineer the Database dialog, tick the schemas and press Load.
If MySQL runs on another computer, choose Remote computer or custom port in step 3, and type its Server Host. Connecting and importing only read from MySQL. Saving the diagram needs DbSchema Pro, included in the 15-day Architect trial.
Free Community Edition plus a 15-day Architect trial. No signup. Windows, macOS and Linux.
What a MySQL ER diagram shows
To see each kind of line, create four tables in a scratch database:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
CREATE TABLE shipments (
shipment_id INT PRIMARY KEY,
order_id INT NOT NULL UNIQUE,
FOREIGN KEY (order_id) REFERENCES orders (order_id)
);
CREATE TABLE reviews (
review_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
After the import, DbSchema draws four boxes and three lines:
Each line runs from the child table to the parent table at the arrow end. The marks at the child end depend on the child column, the column that holds the foreign key:
orders.customer_idis NOT NULL, so the line is solid, and a bar with a crow's foot (three prongs) means one or many.shipments.order_idis NOT NULL and UNIQUE, so a single bar means one shipment per order.reviews.customer_idallows NULL, so the line is dashed, and a circle with a crow's foot means zero or many.
Why a table has no lines
DbSchema draws the foreign keys that MySQL stores. A table without lines has no foreign keys in MySQL, either because nobody declared them, or because its storage engine can't store them. InnoDB and NDB are the engines that support foreign keys. On a MyISAM table, MySQL 9.1 accepts the FOREIGN KEY clause silently:
CREATE TABLE invoices (
invoice_id INT PRIMARY KEY,
order_id INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders (order_id)
) ENGINE = MyISAM;
SHOW CREATE TABLE invoices;
MySQL kept an index on order_id and dropped the foreign key:
CREATE TABLE `invoices` (
`invoice_id` int NOT NULL,
`order_id` int NOT NULL,
PRIMARY KEY (`invoice_id`),
KEY `order_id` (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
To find such tables, list the tables whose engine isn't InnoDB:
SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND engine <> 'InnoDB';
| TABLE_NAME | ENGINE |
|---|---|
| invoices | MyISAM |
To store the foreign key in MySQL, convert the table to InnoDB and add the key:
ALTER TABLE invoices ENGINE = InnoDB;
ALTER TABLE invoices
ADD FOREIGN KEY (order_id) REFERENCES orders (order_id);
If an invoice points at a missing order, ADD FOREIGN KEY fails with error 1452. Then import the database again to see the line.
To draw the line without changing MySQL, drag invoices.order_id onto orders.order_id on the diagram. In the New Foreign Key dialog, tick Virtual and press OK. DbSchema keeps this virtual foreign key in its model only, and saving the model needs DbSchema Pro.
Arrange a large schema
A large imported schema lands on one diagram with its tables overlapping. Both commands below change only the diagram, never MySQL:
- Diagram → Auto Arrange Tables lays the tables out by their foreign keys. If you placed tables by hand, choose Diagram → Duplicate Diagram and arrange the copy. Edit → Undo can't restore the old positions.
- Diagram → New Diagram opens a diagram for one module, such as billing. Pick its tables, press Include >>, then Ok.
Design new MySQL tables
While DbSchema is connected, each change you confirm with OK runs in MySQL at once. To review the SQL first, design offline:
- On the Start New tab, choose Design from Scratch, click MySQL, and press Start Design.
- Choose Table → Create Table, name the table, add its columns, and press OK.
- Drag the child column onto the parent column, and press OK in the New Foreign Key dialog.
- Choose Database → Create or Upgrade Model in the Database, and pick your MySQL connection. This step needs DbSchema Pro.
- DbSchema shows one statement at a time. Press Execute to run it, or Execute All.
Keep the diagram current and share it
A saved model falls behind when a migration changes MySQL. Schema synchronization, in DbSchema Pro, lists each difference between the model and the database, and shows the SQL before it runs.
To share the diagram, choose File → Export As... → Diagram Image for a PNG or SVG file. File → Export As... → HTML5/PDF Documentation writes an HTML5 file with the interactive diagram and a description of every table. The Community Edition documents a diagram of up to 12 tables, and DbSchema Pro any diagram.
Download DbSchema, and import your MySQL schema to get its diagram. Connecting, importing, arranging and image export are free in the Community Edition. Saving the model, synchronization and full documentation are in DbSchema Pro. The DbSchema MySQL page has the connection details.