FK Issue in DDL Generation

[David Ingleton]

Hi, when generating DDL for SQL Server I am seeing issues with the FK generation. Specifically I am seeing FK constraints with empty reference data:

   CONSTRAINT [FK_Claim_ClaimType] FOREIGN KEY () REFERENCES  () ON DELETE No Action ON UPDATE No Action,
   CONSTRAINT [FK_Claim_ClaimType_02] FOREIGN KEY ([ClaimTypeSKey]) REFERENCES [dim].[ClaimType] ([ClaimTypeSKey]) ON DELETE No Action ON UPDATE No Action,

Note in this example 2 instances of FK for Claim_ClaimType. In the model I can only see one association between Claim and ClaimType tables.

I have tried deleting the association but the invalid constraint is still generated so I am guessing there is some sort of corruption here. I have a total of over 60 such errors, mostly arising when there are multiple FKs to the same table (based on different columns).

Any suggestions much appreciated.

[Geert Bellekens]

Two things to try:

- run a project integrity check
- check if you have any leftover «FK» operations on the table.

A foreign key is represented by both an association and an operation on the table.

Geert

[David Ingleton]

Thanks for your reply Geert. I have run the integrity check and corrected all errors but this did not help.
I have found the spurious FK connector with no operation which was causing one of the issues.

However, most of the problems arise when there is more than one FK between the same 2 tables. In these case both the Connector and Property exist but the DDL generation is just not working for the FK constraint

[ducatiross]

Hi David,

I'm having the same problems as you
- duplicate constraints being created in generated DDL when there is no easily identifiable duplicate relationship
- ill-formed constraints in DDL that have no end point, again, where there is no trackable cause.

Did you find a way of identifying these spurious relations in the model ?
Do you know what caused them to appear ?
If the project integrity check didn't clear it, how did you fix the problem ?

Thanks,

Matthew