Generate a custom build statement with PowerDesigner

Original: Create a custom build statement with PowerDesigner

We often use PowerDesigner to design the database table structure, and the design of the table is more intuitive to see the relationship between, easy to understand, but its automatically generated script does not necessarily meet our actual needs, so need to be configured to really meet the requirements, Here's a simple case to learn how to configure PD.

Demand:

　　This assumes that the database code version maintenance is managed through a SQL script file, constructing a repeatable creation table, adding fields, indexes, etc.

Use PowerDesigner to generate scripts that match your actual requirements, as follows

1. Build table statements can be repeated

2. The table name should have Chinese comment

3. In the PD the key association is not reflected in the build script

4. The primary key, foreign key, etc. can be custom named

Test table:

Student table (Student) and Class table (Classes)

The PD is designed as follows:

　

Automatically generate scripts:　　

if exists(Select 1    fromSys.sysreferences RJoinSys.sysobjects o on(o.id=R.constid andO.type= 'F')   whereR.fkeyid= object_id('school_student') andO.name= 'Fk_school_s_reference_school_c')Alter Tableschool_studentDrop constraintFk_school_s_reference_school_cGoif exists(Select 1             fromsysobjectswhereId= object_id('school_classes')             andType= 'U')   Drop Tableschool_classesGoif exists(Select 1             fromsysobjectswhereId= object_id('school_student')             andType= 'U')   Drop Tableschool_studentGo/*==============================================================*//*table:school_classes*//*==============================================================*/Create Tableschool_classes (IDint                   not NULL, Namenvarchar( -)NULL, Createtimedatetime             NULL default getdate(),   constraintPk_school_classesPrimary Key(ID))Go/*==============================================================*//*table:school_student*//*==============================================================*/Create Tableschool_student (IDint                   not NULL, Namenvarchar( -)NULL, ClassIDint                  NULL default 0, Agetinyint              NULL default 0, Stunonvarchar(Ten)NULL, Remarknvarchar( -)NULL,   constraintPk_school_studentPrimary Key(ID))GoAlter Tableschool_studentAdd constraintFk_school_s_reference_school_cForeign Key(ClassID)Referencesschool_classes (ID)Go

View Code

As you can see from the script above

The first time each table exists will drop first and then in Create, in the automatic upgrade script easy to create delete real table;

The second figure on the class number is a foreign key, but it is assumed that only to facilitate the view of the relationship, in real case we may not need to generate foreign key relations;

Third, if the table name is long, the primary key will be truncated or not the format we expect.

So although the table design is good, but to check in the database script, you still need to make some changes, the following step to implement the custom configuration to meet the requirements

Custom Configuration PD

　　1. Remove the foreign Key association from the script

1) Double-click on the table structure, remove the Create foreign key and drop foreign key as shown, then click Apply, you will find the preview foreign key related script has not been

　

　　2. Remove the automatically generated table comments and replace them with the custom

1) Click the database->generate database->format remove the tick before the title, this time the custom generated comments are gone, the next step to add a custom comment;

2) Click Database->edit Current dbms->script->objects->table->create, add the script as shown, this time preview already has this comment

　　3. Allow the build statement to be repeated, such as if not exists create

1) Remove the drop table operation, through the 1.1 show Generation options, remove the drop table tick can be;

2) Add a custom duplicate script judgment statement, or just the location where the 2.2 diagram is located, modify the value values such as Table->create

　　4. Customizing the primary and foreign key names

1) position as follows, where pk_%. U27:table% is the name of the rule of the primary key, U27 is the maximum length is only 27 bits, table is the name, modify here can change the primary key generation rule

With the above configuration, the resulting SQL script will be generated as we have imagined, as follows

/*Table Name: Class Table*/if  not exists(Select 1             fromsysobjectswhereId= object_id('school_classes')             andType= 'U')begin    Create Tableschool_classes (IDint                   not NULL, Namenvarchar( -)NULL, Createtimedatetime             NULL default getdate(),       constraintPk_school_classesPrimary Key(ID))EndGo/*table name: Student Table*/if  not exists(Select 1             fromsysobjectswhereId= object_id('school_student')             andType= 'U')begin    Create Tableschool_student (IDint                   not NULL, Namenvarchar( -)NULL, ClassIDint                  NULL default 0, Agetinyint              NULL default 0, Stunonvarchar(Ten)NULL, Remarknvarchar( -)NULL,       constraintPk_school_studentPrimary Key(ID))EndGo

View Code

In fact, for custom scripts, you should find that most of them are defined by the database->edit current dbms->script->objects, such as table to define tables, column to define columns, A lot of features can be learned just by trying to modify them.

Generate a custom build statement with PowerDesigner