The SQL generator

The SQL Designer module provides services used to generate SQL scripts from table models.

The generated SQL script uses the characteristics of an SQL database schema (create database, create table, alter table …​) and takes into account all the constraints described at table model level.

Generating an SQL script from a table model

To generate an SQL script file:

  • The "4 Generate SQL Script" command available on a DataBase type element is used to produce the SQL schema for this database:

2
The "Generate SQL Script" command of the SQL Designer module

  • A file generation window appears in which you can specify a generation path and some options before clicking on the "Generate" button. Some options are available which allow to modify the structure of the generated SQL script. The effect of these options is describe below:

3
Window in which you can enter a generation path

Code generated for an Oracle database

Options

Yes

No

Generate the Primary Keys in the ‘CREATE’ statement

CREATE TABLE Persons( P_Id int NOT NULL PRIMARY KEY,…​, CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) );

` ALTER TABLE PersonsADD PRIMARY KEY (P_Id); ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName);`

Generate the ‘UNIQUE’ constraint in ‘CREATE’ statement

`CREATE TABLE Persons(P_Id int NOT NULL UNIQUE,…​,CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)); `

ALTER TABLE Persons ADD UNIQUE (P_Id);ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);

Generate the ‘DEFAULT’ key world in ‘CREATE’ statement

CREATE TABLE Persons( …​,City varchar(255) DEFAULT 'Sandnes');

ALTER TABLE Persons MODIFY City DEFAULT 'SANDNES'

Generate the ‘CHECK constraint in ‘CREATE’ statement

CREATE TABLE Persons(P_Id int NOT NULL CHECK (P_Id>0),…​,CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes'));

ALTER TABLE PersonsADD CHECK (P_Id>0);ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes');

Generate the ‘Foreign Keys’ in ‘CREATE’ statement

CREATE TABLE Orders(…​,P_Id int FOREIGN KEY REFERENCES Persons(P_Id));

ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id);ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id);

  • A "LibraryTablesModel.sql" file is then generated.