CODEWITHSUNDEEP
data-modelingtransformationddlenterprise-architect
JMnITup
JMnITup

Reputation: 41

Sparx Enterprise Architect - DDL transformation, many-to-many relationships, and SQL Server 2008

In Enterprise Architect, I have a logical model using UML class and the 'associate' class relationship to build a logical model. I can do a transformation of this model to DDL, and after setting SQL Server 2008 as the default database, this works pretty well.

I am, however, running into some pain managing many-to-many relationships. Lets say I have two tables: Test1 and Test2

Both tables will contain a single attribute, Name: String

I set an association between the two, with the multiplicity on both source and target as 0..*

As expected, when transformed to DDL, this creates a relational table, which is perfect. However, it is the naming conventions I am struggling with.

By default, in the DDL, it will create a table named 'JoinTest1ToTest2', with test1ID and test2ID as values in the new table (matching the generated ID columns in the two actual tables). At first, renaming this was a hurdle, but I realized if I simply put a name into the relationship, it will use that name for the new table. One problem solved.

Now, the other problem I have has avoided such an easy solution. The issue is that it creates 2 foreign keys in the new table, which again is perfect. However, rather than naming these foreign keys like it normally would with an FK_ prefix, it simply calls the foreign keys 'Test1' and 'Test2'. On top of being annoying as it does not follow naming standards, it is also invalid, as this name conflicts with the table names, and the generated SQL fails.

Is there any way to guide the names of the FKs created? By our naming standards, the relational table should be named 'Test1_Test2' and the Fkeys should be 'FK_Test1_Test2__Test1' and 'FK_Test1_Test2__Test2'.

While it is very simple to manually update these after doing a DDL transformation (and after doing this, the DDL Generation of SQL code actually works perfectly), this can be tedious to do in a large model.

Thanks!

Upvotes: 1

Views: 2698

Answers (1)

Uffe
Uffe

Reputation: 10504

In the same options page where you set the default database (Tools -- Options -- Source Code Engineering -- Code Editors), there's a button for DDL Name Templates, which lets you specify templates for foreign keys, primary keys and the unique constraint.

From your question, it would appear that you've lost the correct settings here, as the default template causes foreign keys to be named "FK_" followed by the foreign and primary table names.

The template syntax is described in the help file under Database Engineering -- Physical Data Model -- Data Types -- Database Keys -- Foreign Key -- Define Foreign Key Name Template.

Upvotes: 1

Related Questions