6.7. Model validation

The main goal of this tool is to avoid references or rules breaking during design time as well prevent problems when exporting the model directly to a PostgreSQL server. This mechanism simplified the way pgModeler loads and saves the model files, shortening the execution time of these two operations with the advantage that the validation creates a consistent, reliable and almost error-free model. The validation process is divided in four steps: reference checking, name uniqueness checking, permanent relationship invalidation checking and object's SQL checking.

The first validation, reference checking, will analyze if the objects are being created in the correct order. The basic rule when designing in pgModeler is that all objects must be created in a certain chronological order, but in many cases the user cannot predict this situation and make links between objects without worry about their creation order. This can generate a database model with several reference breaks, which means, objects being created before its dependencies.

The second validation step, the name uniqueness checking, pgModeler is able to verify if tables, views, primary keys, unique keys and exclude constraints have distinguished names if they reside on the same schema.

Now, the permanent relationship invalidation checking will look for the relationships that are permanently broken. A broken relationship in this context is the one which has the column propagation affected in some way so one or more columns aren't created from the reference table to the receiver one (details about column propagation mechanism can be found in section Relationships at Chapter 3). The only solution for permanent invalidation is the relocation of the relationship which means changing the creation order of the object.

The object's SQL checking is the last validation step and it is useful to verify other details on the generated SQL like stored procedures syntax, views definitions and many other aspects not covered by the first three validation steps. An important note here is that this process will not be executed while the model does not pass in the previous validation iterations.

Despite being possible to save an invalidated model this is a risky and not recommended action since it can produce a corrupted model file which will demand fixes through the model fix tool or the command line interface, so in order to avoid problems always validate your model before save it. The validation widget on the image below encapsulates all the validation steps in a single object. All the process is quite verbose which facilitates the identification of errors. After execute the validation process you can manually fix the problem based upon the errors returned or let pgModeler apply automatic fixes for you.

NOTE: The validation isn't a perfect mechanism this because it can't detect none of the validation problems mentioned above in stored procedures sources, custom SQL codes attached to other objects or even in default values of columns. In these cases the user is responsible to fix the problems manually by using the switch objects ids tool (detailed in the next section) which can fix almost all of these kinds issues.
Action/Option Description
Validate This button starts a new validation process on the current open model.
Apply fixes Let pgModeler do the automatic fixes on all problematic objects listed on the output widget. Using this button will trigger the validation process while there are errors listed on the output.
Swap ids This button triggers the dialog where it is possible to swap the ids (creation order) of two objects. Generally there is no need to use this dialog but in cases when you know and want an object to be created before another you can use this resource to avoid errors.
Clear This button will clear the output widget.
Options This button shows or hides the SQL validation options.
Cancel This button is used to abort a running validation process. Aborting a validation will not cause the restoration of the model initial state this because the validation can occurs during the automatic fix process.
SQL validation Enables the validation of the SQL code in the DBMS. This process requires the use of a pre-configured connection. SQL validation will occur only in the last step (when all objects were validated) or when there are no warnings.
Use unique temporary names
for cluster level objects
pgModeler will generate unique and temporary names for database, role and tablespace objects. This option avoids object duplication errors when running the SQL validation.

Aug 31, 2018 at 11:13