MySQL ER Diagram Guide

MySQL schemas come with their own dialect quirks. None of them change the relational model, but all of them matter when the goal is a diagram that tells the truth about the database.

Syntax you will see

• Backtick identifiers — `users`, `order_items`; a parser must unquote them to match tables across statements.
• AUTO_INCREMENT instead of SERIAL — lives inline in the column definition, and implies a key in practice.
• TINYINT(1) — the de facto boolean. A diagram that labels it 'integer' loses information.
• CHAR(36) — where UUIDs hide in older MySQL schemas.
• ENGINE=InnoDB — table options, irrelevant to the diagram, common in dumps.

CREATE TABLE `posts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `published` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `posts_user_id_idx` (`user_id`),
  CONSTRAINT `posts_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Storage engines: InnoDB vs MyISAM

InnoDB has been the default since MySQL 5.5, but older production schemas still contain MyISAM tables, and the difference is not cosmetic. InnoDB enforces foreign keys; MyISAM accepts the FOREIGN KEY syntax and silently discards it — a MyISAM dump can contain REFERENCES clauses that never created a constraint. Decide deliberately on import: keep the declared edges, which usually reflect intent, or drop them and note the gap. MyISAM tables also lack ON DELETE actions; cascades never ran.

• InnoDB: FKs enforced, transactions, row-level locking, clustered on the primary key — the normal case.
• MyISAM: FKs parsed but ignored, no transactions, table-level locking — legacy code, or full-text search before MySQL 5.6.
• Repairing the model usually starts with ALTER TABLE ... ENGINE=InnoDB on the stray tables; the constraints cannot exist until the engine does.

ALTER TABLE `comments` ENGINE=InnoDB;

ALTER TABLE `comments`
  ADD CONSTRAINT `comments_post_fk`
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE;

Charsets: utf8mb4 and what it means for columns

MySQL's utf8 was always a three-byte subset of UTF-8; full support arrived as utf8mb4, the default since MySQL 8.0. The charset rarely changes structure, but it explains odd types you will meet: VARCHAR(255) on utf8mb4 under older row formats collides with the 767-byte index prefix limit, which is why legacy schemas use VARCHAR(191) for indexed columns — a magic number that only makes sense once you know the story. Newer DYNAMIC row formats allow 3072 bytes, so the limit matters mostly on old servers. Collations (utf8mb4_0900_ai_ci on MySQL 8, utf8mb4_general_ci elsewhere) affect comparison and sorting, not the model, so they belong in documentation rather than in the boxes.

Generated columns

Since 5.7, MySQL supports generated columns: values computed from an expression, either VIRTUAL (computed at read time) or STORED (materialized on write). They are common for slugs, concatenated names and JSON extraction helpers. In a diagram they are regular columns marked as generated — the practical fact is that nothing may be inserted into them, so seed scripts written against plain columns will fail.

CREATE TABLE `articles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(220) GENERATED ALWAYS AS (LOWER(REPLACE(`title`, ' ', '-'))) STORED,
  `author_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `articles_author_fk` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysqldump output

A mysqldump without --no-create-info interleaves CREATE TABLE and the constraints (sometimes as separate ALTER TABLE statements at the end of the file). Feed the whole dump, not an excerpt: foreign keys declared after dozens of tables still belong to their edges.

A few dump artifacts are worth recognizing. The file is wrapped in version-conditional comments — lines like /*!40101 SET @saved_cs_client = @@character_set_client */; — which are plain SQL to MySQL and comments everywhere else; a parser must not choke on them. Every CREATE TABLE is preceded by DROP TABLE IF EXISTS, and INSERT batches sit between LOCK TABLES lines. The AUTO_INCREMENT=<n> table option carries the next counter value and is safe to ignore. The constraints come last, as ALTER TABLE ADD CONSTRAINT statements after the data — which is exactly why truncating a dump at the last CREATE TABLE quietly removes most relationships from the diagram.

DROP TABLE IF EXISTS `comments`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
CREATE TABLE `comments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `post_id` INT NOT NULL,
  `body` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `comments_post_id_idx` (`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4821 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
-- ... data and other tables ...
ALTER TABLE `comments`
  ADD CONSTRAINT `comments_post_id_post_id_fk`
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`);

In ERDGenerator, choose the MySQL dialect and paste the dump. Backticks, TINYINT(1) booleans and inline AUTO_INCREMENT are normalized into the same clean model as PostgreSQL — and the export can emit the schema back as MySQL DDL, ready to run.

MariaDB compatibility

MariaDB forked from MySQL in 2009 and the DDL is close enough that most dumps parse as either dialect. Differences worth knowing: MariaDB's JSON type is an alias for LONGTEXT, typically with a json_valid CHECK constraint, while MySQL 8 has a real binary JSON type. MariaDB added a native UUID type and sequences; MySQL has neither — UUIDs live in CHAR(36) or BINARY(16), and sequences are simulated with AUTO_INCREMENT. CHECK constraints are enforced from MySQL 8.0.16 but were parsed-and-ignored in 5.7, so a 5.7 dump can contain checks nobody ever ran. Default values on TEXT and BLOB columns are legal in MariaDB and rejected by MySQL. Generated columns exist in both, with MariaDB accepting a slightly different syntax. For diagramming the ER model is identical; the differences surface on export.

Common mistakes

• Truncating the dump at the last CREATE TABLE. The trailing ADD CONSTRAINT block holds most relationships; without it, the diagram keeps its boxes and loses its edges.
• Reading TINYINT(1) as a small integer. It is a boolean by convention, ORMs and drivers treat it as one, and a diagram that disagrees is wrong.
• Overlooking self-referencing keys. employees.manager_id pointing at employees.id is a normal edge, just drawn back onto its own table — the standard hierarchy pattern.
• Splitting a dump on semicolons. Files that contain stored procedures or triggers use DELIMITER to redefine the statement terminator, and a naive split produces garbage.
• Trusting CHECK constraints from a MySQL 5.7 dump as enforced rules. They were parsed and ignored; the diagram should not imply a rule the server never applied.
• Assuming KEY and CONSTRAINT are the same thing. KEY is a secondary index and matters for query plans, not the model; only FOREIGN KEY lines become edges.

Best practices

• Dump with --no-data (add --single-transaction for a live InnoDB server) so you diagram structure rather than row counts.
• Feed the complete file every time, including the trailing constraint section — partial dumps are the most common cause of edge-less diagrams.
• Name your constraints. MySQL invents names like comments_post_id_post_id_fk; explicit names survive dumps, code review and re-imports.
• Declare ON DELETE and ON UPDATE actions where they exist. The diagram is the cheapest place to notice an unintended RESTRICT.
• Convert stray MyISAM tables to InnoDB first, then re-dump, so the constraints actually appear.
• Standardize on utf8mb4 for new tables. Mixed utf8/utf8mb4 columns in one join silently change comparison behavior, and the diagram should reflect the types as they are.

Frequently asked questions

Does a full mysqldump work, or do I need to clean it first? Paste the file as-is and select the MySQL dialect. The parser handles backticks, conditional comments, LOCK TABLES wrappers and the trailing constraint block, so no preprocessing is needed.

My diagram shows fewer relationships than the database should have. Check the ENGINE clause of the tables involved. MyISAM accepts foreign key syntax without creating constraints, and schemas drift — the referenced table may have been renamed or dropped long ago. The parser draws what the DDL declares, so a missing edge almost always traces back to a missing or ignored clause at the source.

How are composite keys handled? PRIMARY KEY (`a`, `b`) marks both columns, and a FOREIGN KEY (a, b) REFERENCES produces one edge carrying the composite pair. Junction tables in many-to-many schemas are the typical example, and they survive export unchanged.

Can I round-trip the diagram back to MySQL? Yes — export emits DDL in the dialect you imported, with backtick identifiers, AUTO_INCREMENT, TINYINT(1) booleans and utf8mb4 defaults. It is scaffolding and review output, not a replication tool.