How to Generate an ERD from SQL (CREATE TABLE Statements)
Your SQL DDL already defines every table, key, and constraint. Turning CREATE TABLE statements into an ERD is a translation exercise, and this guide gives you the mapping.
SQL data definition language - the CREATE TABLE statements that build your database - is a complete specification of your schema. Every table, column, primary key, and foreign key relationship is right there in the DDL, which makes it an excellent source for an entity relationship diagram. Rather than interpreting a mental model, you translate a precise artifact: each table becomes an entity, each column an attribute, each PRIMARY KEY a key marker, and each FOREIGN KEY a relationship line with the correct cardinality.
This guide gives you that mapping in detail, with SQL examples you can read straight into a diagram. You can build and refine the result in Atlas Diagram Studio at /diagrams using the ERD tool at /diagram-tools/erd-tool, which provides the entity shapes and crow's foot connectors so the translation is mechanical. Because generating from your DDL keeps the picture faithful to the actual database, this workflow belongs alongside the guide on generating diagrams from code at /guides/how-to-generate-diagrams-from-code.
Mapping CREATE TABLE to entities
Each CREATE TABLE statement becomes one entity in the ERD. The table name is the entity name, each column declaration becomes an attribute, and the column's data type carries over for the physical view. The PRIMARY KEY clause identifies the attribute - or set of attributes, for a composite key - that uniquely identifies each row, which you mark with a key indicator on the box. NOT NULL and UNIQUE constraints add modality and uniqueness information worth showing.
Take these two tables, which the ERD reads directly:
CREATE TABLE customer ( id INTEGER PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(255) ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total NUMERIC(10,2) NOT NULL, FOREIGN KEY (customer_id) REFERENCES customer (id) );
Here customer and orders are two entities. Their id columns are the primary keys. The orders.customer_id column is a foreign key, and the FOREIGN KEY clause that references customer(id) is precisely the relationship line the diagram draws between the two boxes.
Turning FOREIGN KEY constraints into relationships
A FOREIGN KEY constraint is the physical embodiment of a relationship, and it maps straight onto a crow's foot line. In the example, orders.customer_id references customer.id, which means each order belongs to exactly one customer while a customer can have many orders - a one-to-many relationship drawn with a single bar on the customer end and a crow's foot on the orders end. The direction is always the same: the table holding the foreign key is the many side, and the table it references is the one side.
The constraints around the foreign key refine the cardinality further. Because orders.customer_id is declared NOT NULL, every order must have a customer, so the modality is mandatory - a bar rather than a circle on that end. If the column allowed nulls, the relationship would be optional, drawn with a circle. A UNIQUE constraint on a foreign key turns a one-to-many into a one-to-one, because each parent could then be referenced by only a single child. Reading these constraints tells you the exact notation without guessing.
Handling junction tables and composite keys
Many-to-many relationships show up in SQL as a junction table - a table whose main job is to hold two foreign keys. Recognizing these keeps your diagram truthful about the underlying structure.
- A table with two foreign keys and little else is a junction table resolving a many-to-many relationship.
- Its composite PRIMARY KEY is usually the pair of foreign keys, guaranteeing each pairing appears once.
- Draw it as its own entity connected by two one-to-many lines, one to each parent table.
- Extra columns on the junction - a quantity, a joined date - are attributes of the relationship and belong on that entity.
- A composite primary key that is not two foreign keys still marks the box as keyed on multiple columns together.
- A FOREIGN KEY with ON DELETE CASCADE hints at an ownership relationship worth noting when you annotate the diagram.
From a database dump to a living diagram
The most common practical case is an existing database you did not design, where a schema dump of CREATE TABLE statements is the fastest route to understanding it. Translating that DDL into an ERD gives you the map of a system you can otherwise only explore one table at a time. Because the diagram comes from the actual schema, it is accurate the moment you make it, and regenerating from a fresh dump keeps it accurate as the database changes - the same anti-drift principle that makes code-generated diagrams valuable.
Bring the translated structure into the editor at /diagrams to make it readable: group tables by domain, name the relationships in business terms, and highlight the core entities so newcomers know where to start. The DDL guarantees correctness; your layout provides comprehension. For a broader treatment of reconstructing a schema you inherited, see the guide on reverse-engineering a database into a diagram, and for the design principles behind a good schema, the database design guide at /guides/database-design.