I completely agree with what you have said. Unfortunately the system is quite old (10+ years) and those who created the physical tables, for some reason, decided not to define foreign keys.
We do not have the luxury of forward engineering the changes at this point. I was wondering if Sparx had something which prevents overwriting the relationships already defined in the model or add only those tables from the database that are new/modified.
Hi Raju,
We have a similar problem (and in this case, the third party software is pretty current). From what we can see, there are over 2000 tables without a single Foreign Key Constraint!
Now to your question. As Geert says, you are (by reverse engineering) creating a physical model. Consequently, every time you reverse engineer, what you should end up with is a "copy" of the physical DB. If you want to create things that DON'T exist in the physical model, then you either have to be able to forward engineer the changes to the physical DB (and thus make the reality the same as the updated model) or you have to change something other than the physical model - because otherwise you can't update the physical model.
About a decade ago, we came to the conclusion that there are TWO models at the physical level. The Design Model (what we want to see in the DB) and the Implementation Model (what is actually in the DB). The Design Model is only ever forward engineered and the Implementation Model is only ever reverse engineered. The two models can (because they are at the same level of abstraction) can be compared and contrasted.
The process we generally use to create the Design Model (where there is only an implementation model), is to export the Implementation Model and re-import changing the GUIDs and tracing back to the implementation model. We can, therefore, make changes (such as you intend) to the design model and that's what we use in normal circumstances.
In any event, since (as I understand it) you can't change the physical DB, you aren't creating Foreign Key Constraints in the implementation model, but merely documenting dependencies between the columns in various tables. You may "get away with" creating your relationships as Dependencies which might NOT be affected by reverse engineering.
HTH,
Paolo
