Back to articles list
Read these troubleshooting tips before using the DDL import option to generate data models in Vertabelo. Avoid running into potential errors that waste your valuable designing time.
Few data modeling tasks start from a blank sheet of paper. More often, we work on a pre-existing database.
You don't want to draw an ERD of an existing database from scratch. But I don’t have to tell you that. This task is much simpler and infinitely faster with a modeling tool that can load the database structure using a DDL import feature.
Vertabelo has this capability – it lets you import a data definition language (DDL) SQL script, a subset of SQL statements for modifying the structure of a database that almost any RDBMS can generate. You probably first want to read about how to use Vertabelo’s import database features to better understand this functionality.
Vertabelo has the option of generating a new physical data model from an SQL script.
Tip 1: Select the Right Parser for Your Database Engine
SQL is a standard language used by all RDBMSs. However, each database engine has its own version of SQL with unique commands that make it incompatible with others. This is one of the most important issues to consider when generating an ERD from a DDL import in Vertabelo.
All SQL database systems are capable of generating DDL scripts from a database. In most cases, RDBMSs save SQL scripts as text files, commonly with a .SQL extension.
When generating a data model in Vertabelo from a DDL script, it is very important to select the appropriate database engine in the Import form. This ensures that the script is interpreted correctly and that the model accurately reflects the database structure.
It is vital to match the selected database engine with the SQL version of the script to be imported.
DDL scripts also vary depending on the tool used to generate them. Some SQL clients are more agnostic than others with respect to the target database. Scripts generated with more database-agnostic tools are more likely to work with different parsers than those generated with specific tools.
For this reason, different kinds of errors may occur in Vertabelo depending on how the DDL script used to generate the data model was generated.
In the following example, I try to generate a physical model with the MySQL 5.x parser using the DDL import function. The script was generated from an SQL Server database using the MS SQL Server Management Studio tool:
/****** Object: Table [dbo].[Employees] Script Date: 3/7/2022 6:13:31 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employees]( [Id] [int] NOT NULL, [FirstName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, [HireDate] [date] NOT NULL, [Email] [varchar](100) NOT NULL, [DateOfBirth] [date] NOT NULL, CONSTRAINT [Employees_pk] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO …
The MySQL 5.x parser makes its best effort to interpret the SQL code of the selected file. However, the script is not compatible, and the result is not what I expect.
In some cases, the parser simply reports that the script contains SQL statements that cannot be interpreted for generating the physical model, as in the following example:
The parser reports all detected SQL errors.
If you instruct the parser to continue building the model by clicking on the "START MODELING" button despite these errors, it results in an incomplete schema. Only the objects the parser recognizes appear. In the example, we get a schema where the names of the imported tables appear but without their corresponding columns.
In the left-hand pane, you can see the validation errors resulting from importing a wrong DDL script.
To avoid this error, select the Vertabelo parser that corresponds to the database engine for which the DDL script was generated.
By using the parser that corresponds to the database, the model faithfully reflects its structure.
Tip 2: Make Sure You Import a SQL File, not a Binary File
Some DDL script generation tools save the scripts in proprietary formats. These files are often more compact than the standard text files used to store SQL scripts and therefore smaller. They also prevent the scripts from unwanted alterations. However, they are not readable by any tool other than the one used to generate them.
Also, RDBMSs allow you to download entire databases in binary files to replicate them on other servers or maintain backups that contain both the database structures and related information. You may use these files to create replicas of a database from a compact file. However, it’s impossible to read and understand the contents of these files with any tool other than the specific RDBMS client that restores those files to replicate the original database.
In general, no binary file, regardless of its origin, can be read by a Vertabelo parser to create a new data model. If you try to do so, you get something like this:
The parser runs into problems when you try to import a non-text file.
Tip 3: Remove Non-DDL Statements From the SQL Script
To successfully import a database into Vertabelo, only DDL statements are needed. Other types of statements may cause problems. Make sure your script only contains CREATE TABLE and CREATE VIEW commands.
The following script contains a combination of DDL statements with INSERT, UPDATE, and DELETE statements:
CREATE TABLE Employees ( Id int NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, HireDate date NOT NULL, Email varchar(100) NOT NULL, DateOfBirth date NOT NULL, CONSTRAINT Employees_pk PRIMARY KEY ( Id ASC ) ); INSERT INTO Employees VALUES (1, 'John', 'Doe', '20220101', 'john.doe@email.com', '19971201');
Vertabelo parsers usually ignore DML commands and only consider DDL commands such as CREATE TABLE. The above script works fine to create a model with the Employees table; the INSERT command is simply ignored. But to avoid confusion and possible errors, it is advisable to remove any non-DDL instructions from the script before using it to generate a data model in Vertabelo.
Tip 4: Avoid Stored Procedure Calls in the SQL Script
An SQL script to create or modify the structure of a database may call stored procedures that alter the tables and the relationships between them. Such a script may be useful for rebuilding the database structure on a server similar to the one hosting the original database. However, it is not appropriate for generating a model in Vertabelo.
For example, the following MS SQL Server script places the DDL statements in a variable to create a database with its tables and relationships. It then invokes the stored procedure sp_executesql, passing it as a parameter to that variable to execute the statements it contains:
USE master; DECLARE @sql NVARCHAR(MAX); SELECT @sql = 'CREATE DATABASE Payroll'; EXEC sp_executesql @sql; SELECT @sql = 'CREATE TABLE Payroll..Employees ( Id int NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, HireDate date NOT NULL, Email varchar(100) NOT NULL, DateOfBirth date NOT NULL, CONSTRAINT Employees_pk PRIMARY KEY ( Id ASC ) )'; EXEC sp_executesql @sql; SELECT @sql = 'CREATE TABLE Payroll..EventTypes ( Id int NOT NULL, EventDescription varchar(30) NOT NULL, CONSTRAINT EventTypes_pk PRIMARY KEY ( Id ASC ) )'; EXEC sp_executesql @sql; SELECT @sql = 'CREATE TABLE Payroll..Timeline ( Id int NOT NULL, EventDate date NOT NULL, EventDetails varchar(50) NOT NULL, EventTypes_Id int NOT NULL, Employees_Id int NOT NULL, CONSTRAINT Timeline_pk PRIMARY KEY ( Id ASC ) )'; EXEC sp_executesql @sql; SELECT @sql = 'ALTER TABLE Payroll..Timeline ADD CONSTRAINT Timeline_Employees FOREIGN KEY(Employees_Id) REFERENCES Employees(Id)'; EXEC sp_executesql @sql; SELECT @sql = 'ALTER TABLE Payroll..Timeline ADD CONSTRAINT Timeline_EventTypes FOREIGN KEY(EventTypes_Id) REFERENCES EventTypes(Id)'; EXEC sp_executesql @sql;
A script like this may be useful for customizing it dynamically. For example, the table and column names can be read from a metadata table to facilitate customization. If you try to use this script in Vertabelo to create a physical model for MS SQL Server, the parser processes it without throwing any error message, since it is a valid SQL script:
The parser tells us it has successfully imported the script.
But the result you get is an empty model.
To solve this problem, remove all the instructions that are not DDL from the script and leave the DDL instructions as static SQL code in the script file. In the example above, this is done by taking the statements that are sent as parameters to the stored procedure and placing them directly in the script without the quotes, so that the parser interprets them correctly.
Also, remove everything that does not help the parser create the model to avoid confusing it. These include CREATE DATABASE statements and schema names used as prefixes in object names that usually appear in MS SQL Server scripts. The above script, corrected, looks like this:
CREATE TABLE Employees ( Id int NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, HireDate date NOT NULL, Email varchar(100) NOT NULL, DateOfBirth date NOT NULL, CONSTRAINT Employees_pk PRIMARY KEY ( Id ASC ) ); CREATE TABLE EventTypes ( Id int NOT NULL, EventDescription varchar(30) NOT NULL, CONSTRAINT EventTypes_pk PRIMARY KEY ( Id ASC ) ); CREATE TABLE Timeline ( Id int NOT NULL, EventDate date NOT NULL, EventDetails varchar(50) NOT NULL, EventTypes_Id int NOT NULL, Employees_Id int NOT NULL, CONSTRAINT Timeline_pk PRIMARY KEY ( Id ASC ) ); ALTER TABLE Timeline ADD CONSTRAINT Timeline_Employees FOREIGN KEY(Employees_Id) REFERENCES Employees(Id); ALTER TABLE Timeline ADD CONSTRAINT Timeline_EventTypes FOREIGN KEY(EventTypes_Id) REFERENCES EventTypes(Id);
If you use the corrected script to create a new data model, the Vertabelo parser displays the message "Successfully imported" just as it had done with the previous version of the SQL import script. But this time, the result is a model that perfectly reflects the structure of the original database:
Other Ways to Model From an Existing Database
Vertabelo offers other ways besides the DDL import to use an existing database for inferring a model structure. For example, you can import DDL scripts to add objects to an existing model. If you need to generate a data model from a database accessible through the Internet, you can use Vertabelo’s reverse engineering feature to generate the model directly from a live database connection instead of using an intermediate DDL script. Vertabelo also has the option to export a database model in an XML file, which can later be imported to generate a new model.
Master Your DDL Import Tool
All tools have their peculiarities. You may think a certain tool is not suitable for what you need when you are not familiar with it. If you are looking for a data modeling tool to generate a model from a DDL script, Vertabelo is the tool for you. Follow the tips above and import DDL files into Vertabelo without any problems.
