Viewing the schema diagram

The Diagram page draws the whole database as an entity-relationship diagram: a box for every table, view and virtual table, its columns inside it, and a line between two boxes wherever a foreign key joins them. It is a way of seeing how a database fits together that a list of tables cannot give you.

The diagram is read-only. It shows the schema; the table designer changes it. Double-clicking a table on the diagram opens it in the designer, which is the quickest way to get from this looks wrong to fixing it, and selects it in the tree as well, so the two panels never disagree about which object you are looking at.

Everything on the page is reached from its context menu — right-click anywhere on the diagram.

What a box tells you

Each row is one column of the table, with its declared type on the right:

• PK marks a column of the primary key, and its name is shown in bold.
• FK marks a column that references another table.
• U marks a column that is unique on its own.
• A ? after the type means the column accepts nulls.

Tables, views and virtual tables are all drawn. Neither a view nor a virtual table can carry a foreign key, so no line ever reaches one — but leaving them out drew part of a schema with no way for the reader to tell which part.

A box that is not an ordinary table says so: its name is in italic, and a tag at the right of its header names what it is. A view says View. A virtual table is named by its module — fts5 tells you more about a box than virtual table would, and it is the one thing the columns do not say.

The shadow tables behind a full-text index stay out — they belong to the index rather than to the schema, and five boxes for one FTS5 table would say nothing about how the database fits together — and so do SQLite's own system tables.

What a line tells you

A line runs from the column that holds the foreign key to the column it references, in the same way a join is drawn in the Query Builder. The mark at each end gives that end's cardinality — one, or many:

• a single stroke where one row is possible;
• a crow's foot where many are.

SQLite does not record cardinality, so SQLite Expert works it out from the keys. The parent end is always one, because SQLite requires the referenced columns to be a primary key or to carry a unique index. The child end is one when the foreign key's columns are exactly that table's primary key or one of its unique constraints, and many otherwise.

A line is solid when the foreign key is part of the child's own primary key — an identifying relationship, where the child cannot exist without its parent — and dashed when it is not.

A foreign key that names a table which is not in the database is drawn as no line at all; the column still carries its FK mark. SQLite allows such a key to be created and only complains when it is enforced.

What the tooltips add

Tooltips carry the two things a picture of a schema is worst at, so they are worth resting the pointer for here more than anywhere else in the program. They are on: what they say is what the diagram cannot draw rather than an explanation of a command, so the Tooltips option in Changing options does not reach them.

Resting the pointer on a box lists its columns with their types, and marks the primary and foreign keys — whatever the diagram is currently set to draw. At the default level a box shows its keys and nothing else, and on the sparsest it shows only a name, so this is often the only place the columns appear. A long list is capped at twenty columns, with a line saying how many more there are: a table of eighty makes a tooltip nobody can read and hides the diagram behind it.

Resting it on a line names the columns being joined — order_lines (order_id) -> orders (id) — and, underneath, the delete and update actions when they are not the default. A line is routed around the boxes and crosses its neighbours, so following one to its end to find out what it joins is exactly the work the tooltip saves.

Where a line crosses a box, the line is what answers: it runs across the boxes it joins, and asking the box first would answer for the box along most of a short line.

Choosing how much to show

Three levels are offered, and the diagram remembers which you last used for each database:

Entities only	Names alone. The shape of the schema, nothing else.
Key columns only	Primary and foreign keys. Enough to follow the relationships without the detail.
All columns	Every column of every table.

Each level keeps its own arrangement, because they are not interchangeable: boxes placed while only the keys were showing would sit on top of each other once every column was. Switching to a level you have not used yet arranges it for you.

Arranging the diagram

Drag a table to move it. The arrangement is remembered with the database, so a diagram comes back the next time you open that file exactly as you last saw it — whether you laid it out yourself or left it as it was arranged for you. A table created since is placed in a row under the rest, where it is easy to find and cannot come up hidden behind something else.

Drag a rectangle across an empty part of the diagram to select several tables at once, and move them as one: dragging any box in the selection takes the whole group with it. A box joins the selection if the rectangle touches it — it does not have to enclose it — and Shift and a click adds a table to the selection or takes it out again. Clicking an empty part of the diagram clears it.

Alt with an arrow key moves whatever is selected a step at a time, which is the way to line two boxes up exactly rather than nearly. The page and home keys move it diagonally.

Moving is all a selection is for here. The diagram shows the schema and does not change it, so there is nothing in it to delete, and every command on the menu applies to the whole diagram rather than to the tables you have selected.

Arrange automatically lays the whole diagram out again from scratch: relationships flow down the page, tables that belong together are grouped, and tables with no relationships at all are packed underneath rather than left scattered. Use it when a diagram has become tangled, or to start from something tidy before moving a few tables yourself.

Boxes cannot be resized. A box is as large as the columns it is showing, so one dragged by a corner springs back to its own size as soon as you let go.

Zooming

Zoom in and Zoom out step by ten percent, and so do + and -, and Ctrl with the mouse wheel. The keys take no modifier: Ctrl with plus and minus is the application's font size, everywhere in SQLite Expert. Coming to the Diagram page puts the keyboard on the diagram, so they work straight away.

Zoom to fit shrinks the diagram until all of it is on screen at once, which is the quickest way to see a schema you have not looked at before. It never enlarges past full size. Actual size, or 0, returns to a hundred percent.

Whatever you leave it at is remembered with the arrangement, so the diagram comes back at the size you were last reading it.

Below about half size the column rows are dropped and only the table names are drawn, because at that scale the rows would be a grey smudge.

Saving and printing

Save as picture writes the whole diagram to a PNG — all of it, not only the part on screen.

Export to PDF produces the diagram across as many sheets of the current paper size as it takes. Export to PDF, single page puts it on one page the size of the diagram instead, which is usually what a schema is wanted as.

Print and Print preview send it to the printer, breaking it across sheets in the same way, and Page setup chooses the paper and margins. The preview window can export to PDF as well.

Whichever of these you use, the diagram is rendered black on white regardless of the theme you are working in. A dark theme put on paper as it stands would be pages of black boxes.

Note

The Diagram page is only in SQLite Expert Professional.