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 tool can generate SQL script that leads your database to the current state of your diagram.
You can synchronize database in two ways:
§Directly execute a modification script on a PostgreSQL server. Please examine Connect to a Database section to explore the database connection process;
§Generate a modification script for executing at a PostgreSQL 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, that database modification usually cause 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.
That's how the Database Modification tool works:
1. Reverse engineers your existing PostgreSQL database
2. Compares the result with your current database diagram
3. Creates a list that contains differences between database objects and diagram objects
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 PostgreSQL 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.
Database Modification Tool
To modify your database, start Database Modification tool by selecting the Database | Modify Database menu item or pressing Ctrl-M.
Interface of the Database Modification dialog is much the same as the Database Generation dialog. The only differences are persist on the Options tab.
Modification Options
In the Options tab of the Database Modification tool you can set modification options.
Don't delete existing tables
This option disables deleting tables that already exist in the database, but don't exist in your diagram.
Backup DB tables before modify
This option enables creating backup of tables to be modifed.
Trace table renaming
Try to find tables that has been renamed in the diagram by comparing their structure with the structure of the tables in the database. If a structure of a table in the database is identical to a structure of a table in the diagram, but such tables have different names, a table in the database is going to be renamed to match the diagram table's name.
Selecting objects to generate
You can select diagram objects which need to be modified in the database. Use the Selection tab of the Database Modification tool for this.
There are several subtabs: Tables, Views, Stores Routines, Types & Domains and Sequences. Each of which allows to select appropriate diagram objects to modify.
To enable particular objects generation, click on the checkbox near it.
The default selection of objects to generate depends on their Generate property. Pay attention to the buttons on the Selection tab:
Select ALL - checks on all checkboxes
Deselect ALL - checks off all checkboxes
Use graphical selection - checks on checkboxes for objects, depending on diagram selection.
You can change the order of tables in which they will be placed in the generated SQL script. Use the buttons with arrows for this.
Generating, customizing and executing SQL
Click on the OK button on the Database Modification tool to generate SQL script. The generated SQL script will be stored in the file you have set. Also 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 section to know more about it.
See also:
Database Accessing: SQL Executor | Connect to a Database