SQL to ER Diagram: The Complete Guide

You inherit a database with 40 tables and no documentation. The DDL is right there — the diagram is just hiding inside it. Turning SQL into an ER diagram is a mechanical mapping, and understanding it helps you read diagrams faster and catch schema problems earlier.

What maps to what

• CREATE TABLE → an entity (box) in the diagram, with its columns listed inside.
• PRIMARY KEY → the PK badge on a column.
• REFERENCES / FOREIGN KEY → an edge, pointing from the child (FK side) to the parent (referenced side).
• UNIQUE → the U badge; a single-column UNIQUE FK often signals a 1:1 relationship.
• ON DELETE CASCADE / SET NULL → the referential action drawn or noted on the edge.
• A table whose primary key is exactly two foreign keys → the materialization of a many-to-many.

Inline and ALTER TABLE foreign keys

Foreign keys appear in two places. Inline, inside CREATE TABLE (user_id INTEGER REFERENCES users(id)), or separately, the way pg_dump and mysqldump emit them: ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY. Both styles describe the same edge — a good parser reads both, which matters because real-world dumps almost always use the second form.

ALTER TABLE ONLY public.posts
  ADD CONSTRAINT posts_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;

A complete example

Here is a small order-management schema as it might arrive from a real dump — inline constraints, an ALTER TABLE constraint, a composite primary key, and a referential action. Everything a diagram needs is in these lines:

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku VARCHAR(40) NOT NULL UNIQUE,
  price NUMERIC(10,2) NOT NULL
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  ordered_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity SMALLINT NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

ALTER TABLE ONLY orders
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers(id);

Reading it as a diagram: customers and orders in a 1:N, orders and products in N:M through order_items, and the edge from orders back to customers carrying no action — meaning a delete of a customer with orders fails with a foreign key violation, which is the RESTRICT default doing its job.

Dialect quirks that affect the mapping

The mapping above is universal, but each dialect writes the same schema differently, and a parser has to cope with all three.

• PostgreSQL: schemas qualify table names (public.orders), identity columns come as SERIAL or GENERATED ALWAYS AS IDENTITY, and pg_dump wraps everything in SET statements and emits constraints after all tables — the parser must read the whole file, not stop at the first error.
• MySQL: unsigned columns (INT UNSIGNED), TINYINT(1) standing for boolean, backquoted identifiers, ENGINE=InnoDB at the end of each table, and foreign keys declared inside CREATE TABLE more often than in ALTER TABLE.
• SQLite: no ALTER TABLE ADD CONSTRAINT at all — foreign keys are always inline, composite types are loose (a column declared INTEGER can hold anything), and enforcement is off unless the connection runs PRAGMA foreign_keys = ON. The SQL often encodes fewer guarantees than the application relies on.

What a diagram intentionally ignores

Several pieces of DDL have no place in an entity-relationship picture, and dropping them is what keeps it readable: secondary indexes, CHECK constraint bodies, trigger definitions, views, sequences, and storage or partitioning clauses. They matter — but in a schema reference, not in the map of relationships.

When the SQL is not enough

Some databases have no declared foreign keys at all — the relationships exist only in application code (order.customer_id = customers.id, joined by convention). In that case the SQL-to-diagram mapping produces boxes with no edges, which is a finding, not a tool failure: the schema has no integrity guarantees, and every delete in production is a gamble. Fix the schema if you can; if you cannot, tools that infer relationships from column names (customer_id → customers) get you a diagram, clearly labeled as inferred.

Common mistakes

• Parse only inline constraints and you silently drop every ALTER TABLE edge from a pg_dump — the diagram then looks disconnected when the database is not.
• Assuming a UNIQUE FK always means 1:1. It does when the FK is NOT NULL; with NULL allowed it is a 0..1 — 0..1 and the diagram should show optional ends.
• Ignoring composite foreign keys. (order_id, product_id) referencing a two-column PK is legal, common in join tables, and invisible to naive single-column parsers.
• Letting dialect noise break the parse: backquotes, IF NOT EXISTS, quoted identifiers with spaces, comments, functions in defaults.
• Reading a diagram from a stale dump. If migrations have moved on, the picture documents a database that no longer exists.
• Forgetting the referenced-side schema in PostgreSQL (public.users vs another schema) and drawing two separate boxes for one table.

Best practices

• Generate the diagram from the same source your migrations come from, and regenerate it as part of the release routine — documentation that updates itself stays accurate.
• Dump with the structure flag only (pg_dump --schema-only, mysqldump --no-data): the data adds megabytes and changes nothing in the diagram.
• Declare your foreign keys in the database, even if the ORM also knows about them. The diagram, the integrity, and the next developer all benefit.
• Keep one dialect per diagram; cross-database schemas confuse the mapping and the reader.
• Check for orphan tables — boxes with no edges. They are either genuinely standalone (settings, logs) or missing relationships.
• Export both a PNG for documentation and SQL for the target dialect when migrating; round-tripping through the diagram catches typos early.

Frequently asked questions

Can I diagram a production dump without leaking data?

Yes — a schema-only dump contains no rows, only DDL. Use pg_dump --schema-only or mysqldump --no-data and what you paste into a tool is table definitions, nothing else. The one thing to watch is default values and comments: if someone put real emails into a CHECK constraint or a column comment, strip them first.

My dump has 200 tables. Do I diagram all of them?

Rarely. Diagram the module you are actually working on — orders and everything it touches, not the whole system. Filter the SQL before pasting (or after generating, if the tool lets you hide tables), and produce one focused diagram per bounded context. Two dozen readable diagrams beat one unreadable one.

How do I get an ER diagram from an existing PostgreSQL database?

Run pg_dump --schema-only --no-owner --no-privileges mydb > schema.sql and paste schema.sql into ERDGenerator's SQL tab with the PostgreSQL dialect. Every table, key, unique constraint and referential action in the dump maps to the diagram exactly as described above — no manual drawing involved. The same flow works for MySQL with mysqldump --no-data and for SQLite by feeding it the .schema output.

Does the reverse also work — diagram to SQL?

It does, and it is a legitimate workflow: sketch the entities and relationships, then export the DDL and refine it with constraints and indexes. The two directions meet in the middle — DBML exists precisely as a text format meant to be authored, read and converted in both ways.