Changes In v5.3 Have Impacted Model To Database Generate Change Script Function

dan.anselm · October 7, 2014, 6:38am

Hi,

I have an existing model from v5.2 (and older) that I use for a SQL 2008 R2 database. I have just upgraded to v5.3 and now if I run a Generate Change Script, I get a huge number of differences in the results window that were not present in v5.2 (i.e. they are not really differences that require changes to the database, they are presumably differences that have occurred now that the reverse engineering scripting engine has been changed).

Differences that now show up in v5.3 as compared with v5.2:-

For every index on every entity, the following properties are now found on the database but have no corresponding value on the model

Relation Source
Foreign Key Source

For every Relation, the following properties are now found on the database but have no corresponding value on the model

Foreign Index
Every Foreign Key Index Item

These are all false positives as compared with the previous version of Toad DM (especially with the Index Item one since I know the index has its items set correctly in the model!), but there are so many of them they make doing a simple comparison very difficult and error prone.

What can I do?

Thanks

Dan

Lukas.Knapek · October 8, 2014, 6:38am

Hello Dan,

TDM 5.3 has a new system of creating indexes to foreign keys. There is a new option that, when enabled, creates these indexes automatically. It can be set either for each specific relationship or for entire model. Note that this option is enabled by default in 5.3.

Indexes created by this function have additional properties, they are linked to a relationship. Indexes you've created in older version of TDM have no such properties, they could be created manually only. That's where the differences found in Model Compare come from.

Now, since you've created your model in older version of TDM, it uses the old system of FK indexes. And when you compare this model to a database (during change script generation), what TDM does is it actually reverse engineers the database to a model and compares the two models. The reverse engineered model uses the new system of FK indexes (since it is enabled by default) and that's why you see those differences.

There are two ways to get rid of those differences.

1. Use the new system of FK indexes in your old model

This is done by opening your model in TDM 5.3, right-clicking on it in Model Explorer and selecting the Infer Indexes of Foreign Keys option. The function will link your FK indexes with your relationships. The advantage of this new system is that whenever you make changes to the relationship between your primary key and foreign key, the index will change accordingly.

2. Disable the new function in your new models

By unchecking the Index to Foreign Key in Child Entity option in Options | Physical Model, you will assure that new models will use the old system of FK indexes. This means that when you try to generate change script next time, there will be no more differences. On the other hand, you also lose the advantage of the new system.

You can choose any of the two systems depending on what works best for you.

Regards,

Lukas

dan.anselm · October 8, 2014, 6:44am

Hi Lukas,

Many thanks for the very detailed explanation J

I will have a proper read through to understand which option to choose and get back to you with my results!

Cheers
Dan

From: Lukas Knapek [mailto:bounce-LukasKnapek@toadworld.com]
Sent: 08 October 2014 14:39
To: tdm@toadworld.com
Subject: RE: [Toad Data Modeler - Discussion Forum] Changes In v5.3 Have Impacted Model To Database Generate Change Script Function

RE: Changes In v5.3 Have Impacted Model To Database Generate Change Script Function

Reply by Lukas Knapek

Hello Dan,

TDM 5.3 has a new system of creating indexes to foreign keys. There is a new option that, when enabled, creates these indexes automatically. It can be set either for each specific relationship or for entire model. Note that this option is enabled by default in 5.3.

Indexes created by this function have additional properties, they are linked to a relationship. Indexes you’ve created in older version of TDM have no such properties, they could be created manually only. That’s where the differences found in Model Compare come from.

Now, since you’ve created your model in older version of TDM, it uses the old system of FK indexes. And when you compare this model to a database (during change script generation), what TDM does is it actually reverse engineers the database to a model and compares the two models. The reverse engineered model uses the new system of FK indexes (since it is enabled by default) and that’s why you see those differences.