The DDL Preview and Execution dialog applies the table design you changed in ERDs and the explorer to the real database. It does not run anything right away: it first shows the DDL it will run, and runs only the DDL of the tables you select. Why saving and applying to the database are separate steps is explained in ERDs and the real database.
Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects
What's on the screen
| No. | Name | Description |
|---|---|---|
| 1 | Target | The DBMS, name and schema of the connection the DDL runs against. |
| 2 | Include schema name | When on, the schema name is put in front of table names in the DDL. See 'Preview options' below. |
| 3 | Table list | The tables to apply. The tags mark tables to create (CREATE), alter (ALTER) and drop (DROP). The DDL of unchecked tables is not run. Select All turns them all on or off. |
| 4 | DDL Preview | The DDL of the checked tables, in the order it will run. It is read-only. |
| 5 | Copy SQL button | Copies the DDL in the preview, to run it in another tool or hand it over for review. |
| 6 | Execute DDL button | Runs the DDL of the checked tables. |
Applying changes to the database
- Open the dialog. Which tables it applies depends on where you open it (see 'What each entry point applies' below).
- In the table list (3), uncheck the tables you do not want to apply this time.
- Check the statements in the DDL preview (4). If you drop columns or change types, a Warning box appears below.
- Click the Execute DDL button (6). When the confirmation shows what will run (the number of tables and DDL statements for several tables), confirm it.
- When everything succeeds, the 3 DDL statement(s) executed successfully. notification appears and the dialog closes. The applied tables are read back from the database and saved, so the change colors in the tables disappear.
What each entry point applies
| Entry point | Tables applied | When you can use it |
|---|---|---|
| The Apply Changes to DB... button in the toolbar that appears above a selected table | That table | The connection has database access details and the table has changes not yet applied |
| The Apply Changes to DB... button in the info panel | Every table in this ERD with changes not yet applied | Shown in an ERD that has a connection; enabled when the connection has database access details and at least one table has changed |
| Right-click a table in the ERD › | The right-clicked table, or every changed table among the selected ones | Shown when the right-clicked table has changes not yet applied |
| in the explorer | The right-clicked table, or every changed table in a connection, schema or table group | When there are changed tables in it. Not shown for a connection without database access details |
When several tables are applied, the menu shows the number of tables (Apply Changes to DB (3)...).
Preview options
- Include schema name is off at first, and on when the tables span several schemas. For SQL Server it is always on and cannot be turned off, because SQL Server cannot switch the session's schema; this is what makes tables land in the right schema when it is not the default one.
- When tables are dropped and the DBMS supports it, Include CASCADE appears. When on,
CASCADEis added toDROP TABLE, which also drops other objects that depend on the table. - Wherever you open the dialog from, after changing a parent table's primary key, the child tables that refer to that key are added to the list with the Auto-included tag. They cannot be unchecked, because their foreign keys must change at the same time.
- Changes that cannot be turned into DDL automatically are listed with an info icon, and the reason appears in the Changes requiring manual action box. Handle them yourself in the SQL editor.
- When a foreign key refers to a table that does not exist in the database yet and is not part of this apply, the Referenced Table Missing dialog asks first, before the preview opens. The Create Referenced Tables Too (1) button also creates the referenced tables (it appears only when they can be created together), and the Apply Without These FKs button applies everything except those foreign keys. The left-out foreign keys stay as changes not yet applied.
When a statement fails
- When a statement fails, the SQL execution error dialog shows which statement it was and the database's error.
- The Retry button sends the statement again, and the Stop button does not run the remaining statements. When statements remain, the Skip button (continue without that statement) and the Skip all button (also skip any later errors) appear as well.
- When only part was applied, the Partial Success notification tells you how many statements were applied, and the dialog stays open. The failed and succeeded statements are marked in the preview, and failure and completion icons appear in the table list.
- The applied part is read back from the database into the design. Fix the remaining changes and run again.
Applying through approval
In an account project, a member whose role has the DDL Restriction permission cannot run DDL directly and requests approval instead. Local projects have no such restriction. How to set permissions is explained in Collaboration and permissions.
- The Apply Changes to DB... button in the info panel is shown in orange. Clicking it lists only the tables you changed.
- The Execute DDL button (1) cannot be clicked. Select the tables to apply and click the Request DB Apply button (2).
- In the DDL Apply Request dialog, write the reason in the Reason field and click the Submit button. The request cannot be sent without a reason.
- A member who can approve reviews and runs it in the panel. See DDL approval.
Things to keep in mind
- Running from this dialog is subject to the connection profile's protection mode. If the protection mode is read-only, nothing runs; if it is confirm, you are asked once more before running. The dangerous SQL confirmation does not apply, so always read the DDL in the preview. Which safeguard applies to which execution path is explained in Safeguards for production databases.
- Selection is per table. To run only some statements of a table, copy them with the Copy SQL button and run them in the SQL editor.
- Whether earlier statements stay in the database after a failure depends on the DBMS. DBMSs that commit DDL immediately (MySQL, MariaDB, Oracle and others) keep everything up to the failed statement, while DBMSs that run DDL in a transaction (PostgreSQL, SQL Server, SQLite and others) roll back all the statements sent together. Check how far it got in the notification and the marks in the preview.
- Applying a table drop deletes the table and its data from the database, and cannot be undone. With Include CASCADE on, other objects are deleted too.
Related screens
- ERD canvas: the screen where you place tables and draw relationships.
- Import tables from DDL: create virtual tables from DDL statements.
- DDL approval: review and run approval requests.
- ERDs and the real database: the difference between saving and applying, and the two ways to delete a table.