An ERD in NeoSQL is more than a drawing. It is where you design and change the tables of a real database. Knowing where your ERD changes are saved and when they reach the database helps you avoid two common surprises: saving and finding the database unchanged, and dropping a table you only meant to take off the diagram.
There is only one model
NeoSQL does not keep a separate logical model and physical model. The tables in an ERD are the same physical model that is created in the database, and the human-readable names (logical names) are descriptions attached to it. Keeping two models means that a change to one is easily missed in the other, and they soon drift apart.
- The Physical mode and Logical mode buttons in the info panel only change which names are shown for the same model. They do not change tables or columns.
- Logical names are not written to the tables created in the database. They are stored separately from column comments.
The three steps a change goes through
When you change a table in an ERD, the change goes through the three steps below. The first two save only to your NeoSQL project; the database changes only in the last step.
| Step | How | What is saved | Database |
|---|---|---|---|
| Save the ERD | The Save button in the info panel, or Ctrl+S (Cmd+S on macOS) | Table positions, notes, groups, display settings and the tables included. The design of changed tables is saved too | Unchanged |
| Save a table design | The Save button in the toolbar that appears above a selected table | The columns, keys and indexes of that table | Unchanged |
| Apply to the database | The Apply Changes to DB... button | DDL generated from the difference between the saved design and the database; only the statements you select are run | Changed |
- Changes not yet applied to the database are shown in color inside the table: new columns are green (1) and changed cells are blue. The colors stay after you save, until you apply the changes. Virtual tables that do not exist in the database are not colored.
- The orange dot to the right of the table name means there are unsaved changes. It disappears when you save. Hovering over the dot turns it into the × button that removes the table from the ERD.
- The Save button in the info panel (2) saves the ERD and the design of changed tables to the project.
- The Apply Changes to DB... button (3) does not run DDL right away. It first shows the DDL it will run; the database changes only after you select the statements and run them.
How tables get into an ERD
A table in an ERD either already exists in the database or does not exist there yet (a virtual table). How it got into the ERD decides what DDL is generated when you apply changes.
| How | Result | When applied |
|---|---|---|
| Drag a table from the object explorer onto the canvas | A table that exists in the database | Only the changed parts are altered with ALTER |
| Place a table with the Add New Table tool | A virtual table | Created with CREATE TABLE |
| Paste DDL into the Import Tables from DDL dialog | A virtual table | Created with CREATE TABLE |
Bringing database tables into an ERD is also called reverse engineering, and applying an ERD design to the database forward engineering.
Remove from ERD versus Delete Table
The two names look alike but do very different things.
| Remove from ERD | Delete Table... | |
|---|---|---|
| What it does | Takes the table off this ERD diagram | Deletes the table from the database |
| Database | Left as it is | If you choose Flag Only (Keep in the database), the table is only marked and deleted at the next apply. If you choose Execute DROP TABLE (Delete from the database), the DROP statement is shown right away and then run |
| Undo | Drag the table in again from the explorer | Undo the mark with the Cancel Table Deletion button |
- Selecting a table on the canvas and pressing
Deleteremoves it from the ERD. It does not delete the table. - A virtual table that does not exist in the database yet is removed immediately when you click Delete Table....
Relationship lines are foreign keys
A relationship line in an ERD is a drawing of the child table's foreign key (FK). Drawing a relationship adds the FK columns and constraint to the child table's design, and the actual FK is created when you apply changes. Selecting a relationship line and pressing Delete removes that FK from the design.
Things to keep in mind
- Saving does not change the database. To apply your design to the database your team or applications use, you must run the DDL with the Apply Changes to DB... button.
- The Refresh button in the toolbar reloads the current table definition from the database. It asks what to do with changes not yet applied (merge or replace). If you choose replace, those changes are all lost.
- The Apply Changes to DB... button runs DDL against production databases too. Always check the DDL it shows before running it.
Related pages
- ERD canvas: the screen where you place tables, draw relationships and arrange the diagram.
- Apply changes to the database: the dialog that previews and runs DDL.
- Import tables from DDL: the dialog that creates virtual tables from DDL statements.
- Document an existing database as an ERD: the steps to bring database tables into an ERD and organize them.