Orphaned connections/relationships

[ducatiross]

Hi everyone,

I posted this comment elsewhere but thought I better start a new thread.

When generating DDL from a PDM, we are experiencing issues with 'orphaned' relationships - those that appear on one side of the connection but where the other is lost. We also cannot see these relationships in the model using any of the standard stuff (relationship matrices, relationship window etc) but they appear when we generate DDL from the PDM .

The DDL also contains duplicate foreign key constraints that we can find no way of locating in the model or understand how they got there.

Has anyone else experienced this, understands what the cause is or how to resolve it ?

Thanks in advance,

Matthew

[Geert Bellekens]

Matthew,

If you have "real" orphaned relations, the project integrity check should find and fix those for you.

But are you sure that is what is happening here? With orphaned I mean relations where either the start or end object ID don't reference an existing record of t_object.

Geert

[ducatiross]

Hi Geert,

Thanks for responding. The reason I think they are genuine orphans is that the DDL generated from the physical model has a number of confusing entries on the integrity constraints :

Example 1
ALTER TABLE [ssu].[value_significant_code] ADD CONSTRAINT [FK_value_significant_code_02]
   FOREIGN KEY ([value_significant_code]) REFERENCES [ssu].[reference_code] (

Code: [Select]

) ON DELETE No Action ON UPDATE No Action
GO

The item [code] does not exist in the model so this reference is erroneous - but why did the DDL transform generate it ?

[u]Example 2[/u]
ALTER TABLE [ssu].[value_significant_code] ADD CONSTRAINT [FK_value_significant_code_01]
FOREIGN KEY ([nda_id]) REFERENCES  () ON DELETE No Action ON UPDATE No Action
GO

The 'REFERENCES' element is missing  - just has () - why was this generated ?#


[u]Example 3[/u]
ALTER TABLE [ssu].[value_significant_code] ADD CONSTRAINT [FK_value_significant_code_su_time_bounded_nda_set]
FOREIGN KEY ([nda_id]) REFERENCES [ssu].[su_time_bounded_nda_set] ([su_time_bounded_nda_set_ID]) ON DELETE No Action ON UPDATE No Action
GO

This is a duplicate foreign key constraint of the next item - why have two been created in the DDL ?

ALTER TABLE [ssu].[value_significant_code] ADD CONSTRAINT [FK_value_significant_code_su_time_bounded_nda_set]
FOREIGN KEY ([nda_id]) REFERENCES [ssu].[su_time_bounded_nda_set] ([su_time_bounded_nda_set_ID]) ON DELETE No Action ON UPDATE No Action
GO

So, the only reasonable explanation for examples 1 and 2 are orphaned relationships.
For example 3, there must be two relationships in the model, but only one appears in any diagram, traceability window or relationship matrix.

If the Project Integrity check can clear these issues, then great. Maybe I should run it before generating any DDL as to only find the issues once we try to run the DDL to create a database is a bit of a pain.

Would my conclusions seem reasonable ? And how do you think relationships get orphaned in the first place ? I suspect unreliable network connects (we have our Sparx DB on an Azure SQL Server instance).

Thanks,

Matthew


[Geert Bellekens]

yeah, the thing is that FK's are in fact a combo of both an association, and an operation.

If one of them gets out of synch, EA looses it pedals
So I think you probably have that. I know I faced that problem regularly when doing reverse engineering of the database.
For some reason, sometimes EA messes things up.

My solution usually is to delete the FK relation (and operation), and then synch with the database again to restore it.

Geert

[ducatiross]

Thanks Geert - I'll check both the relationships and the operations. At this point, I'm not reverse engineering anything from an instantiated database, I'm just using the logical model transformation to a physical model, and then generating DDL from that physical model to use to set up my instantiated database, but something spooky has to be happening. I am not that familiar with operations, so I will look into it.

Thanks a bunch,

Matthew