My organization recently upgraded from TDM 4.3 to 5.0.5. Prior to upgrade I had made a modification to the MSSQL DDL Generation script to generate disabled foreign keys using the WITH NOCHECK option. The reason for this is that we export the foreign keys separate from the DDL and we run the script on databases with data.
I modified the PERCodeGeneratorMS script to accomplish this (unlocked, modified, saved package) and it was working great in TDM 4.3 but modifying this script in TDM 5.0.5 seems to have no effect. Please help!
I was able to get this to work by unchecking Entities and doing a Save Settings and then rechecking Entities. I also edited the scripts for both SQL Server in general and the specific SQL Server version. Hope that helps!
Hi,
TDM version 5.0 includes new system for generation of DDL scripts for SQL Server models (and the next version will contain similar improvement for Oracle databases). The key benefit is that generation should be significantly faster, disadvatage is that custom changes won't be applied.
If you need to change the generation, we can modify the product and add the required functionality for all other users.
To the solution: you can generate SQL code in two steps.
-
Generate database objects without relationships (uncheck the Relationships checkbox on tab What to generate)
-
Generate only relationships (check the Relationships checkbox only, see attached screenshot)
Tip: Enable the Append To File checkbox to append the second part of SQL to the same file.
Regards,
I’m sad to hear that the ability to customize the DDL generation scripts has been removed.
The functionality that I need is the ability to generate foreign key constraints using the WITH NOCHECK option. This creates the foreign key as “not trusted” meaning the existing data is not validated at the time the constraint is created.
A checkbox on the relationship window labeled “Generate as not trusted” would be useful.
Example:
Original script:
ALTER TABLE [tablename] ADD CONSTRAINT [constraint_name] FOREIGN KEY …
With option checked:
ALTER TABLE [tablename] WITH NOCHECK ADD CONSTRAINT [constraint_name] FOREIGN KEY…
Hi,
the SQL script generation cannot be modified for SQL Server models, but it is very easy to change property of all the relationships at once. In other words, you can disable the foreing keys constraints, generate sql. Then enable all the foreign key constraints and generate the sql when necessary.
You can download custom package with macros that I created for you from:
www.toadworld.com/.../654.aspx
Regards,
Disabled and Not Trusted are two different properties of the constraint. Disabled means the constraint is not checked on future inserts. Not trusted means the existing data was not validated when the constraint was created. In sys.foreign_keys these properties are represented by the columns “is_not_trusted” and “is_disabled”. TDM does not currently have a way to create a foreign key that is not trusted.
I made the following change in order to generate all disabled foreign keys as also not trusted:
PERCodeGeneratorMS, Line 1947:
text += "ALTER TABLE "+ChildEntity.QuotedFullName(Instance);
if (Relation.DisabledConstraint)
text += " WITH NOCHECK";
text += " ADD ";
if (Instance.GenerateConstraintNames)
text += "CONSTRAINT “+Relation.QuotedFullName(Instance)+” ";
text += “FOREIGN KEY (”;
…
…
Hi,
thanks for sending us more info. I added new change request to our system. The next version should contain support for Not Trusted property. CR#110634, status “Open”.
Regards,
Vaclav
