Database Modification

Database Modification Overview

Once you have changed your diagram, it's usually necessary to apply these changes to your database. It's easy to do with the Database Modification tool. The Database Modification is generation of SQL script that leads your database to the current state of your diagram.

Please note:
Database modification usually causes multiple complex statements for database structure modification. It is possible that some of them may not execute correctly due to some physical reason. It's recommended to make a backup of your database before applying structure changes to database.

You can modify database in two ways:

▪	Directly execute a modification script on a MySQL server. Please examine Connect to a Database section to explore the database connection process;

▪	Generate a modification script for executing at a MySQL server some time later.

In both cases, the database modification commands are saved in a script file. You must always provide the path to the script file.

Please note:
Sometimes you need to apply changes in your diagram to physical database without leading whole database structure to current state of your diagram. For example if there are some objects in database that are not covered by your diagram or if you want to apply only particular set of diagram changes to database. You can do this using Diagram Revisions and Compare Diagram tool. Please take a look at Comparing Diagram Revisions topic for details.

That's how the Database Modification tool works:

1. Reverse engineers your existing MySQL database.

2. Compares the result with your current database diagram.

3. Creates a list that contains differences between database objects and diagram objects. Then lets you to examine this list and to select changes you want to apply to database.

4. After analyzing the difference list, creates necessary SQL statements, that modify database structure.

If some table is going to be modified, Database Designer for MySQL makes a backup copy of that table, so you can restore data and table structure later on if there will be some errors during the table structure alteration. Those backup tables have _tmp_ string appended before their names. For example `Cars` table will have `_tmp_Cars` backup copy.

Please note:
Opposite process of applying physical database changes to diagram is described in Diagram Synchronization topic.

Step by step guide

To modify your database, start Database Modification tool by selecting the Modify Database ( ) item on Database tab of the Ribbon or pressing Ctrl+M.

Database Designer for MySQL asks you with Database Connection Manager what database you want to modify.

dialog-db-connection-manager

After connecting to selected database Database Designer for MySQL performs Reverse Engineering of this database, compares it with diagram and shows Compare Result dialog with all differences found.

dialog-compare-result

You can check or uncheck particular changes you want to generate SQL code for. Then press Generate SQL button to open Modify Database Options dialog.

dialog-modify-database-options

There are following options on SQL Generation tab:

Don't rename database
Check this option to prevent database renaming even if physical database name differs from one set in diagram.

Don't delete backup tables
If this option is set Database Designer for MySQL will not generate DROP TABLE statements for backup table copies (with _tmp_ prefix) after altering tables structure.

Settings on Options tab are the same as on Options tab of Database Generation tool.

Press OK button to generate SQL script. SQL Executor with generated SQL statements for database modifications will appear. You can easily customize statements according to your wants and wishes. And then send them to the database server by clicking on the Execute SQL button. Please, examine SQL Executor topic to know more about it.