Problem with SQL Script generated

[sylsau]

Hi,

I use EA to do a data model.

In the tables of my model, I created foreign keys. They are well created because I can see them on the diagram layout.

But, when I generate the script SQL on my data model, even if I have checked the choice to generate Foreigne Keys for my tables, the SQL file generated doesn't contain the foreign keys of my tables.

Only the primary keys are in the file.

Do you know what is the problem ?

If you want more information on my problem, tell me the things that I must explain with more details.

Thanks for you help.

[«Midnight»]

Check the DBMS your tables are set to.

You can set the default DBMS for an EA model from the main menu via Settings | Database Datatypes. At the top of the dialog you'll see a drop-down of DBMS products. Select yours and check the Set as Default box. [This box will be checked if you've already set this option, but the drop-down does not show the default on entry to the dialog for some reason.]

Note that this setting is specific to each model. You must have a model open to invoke the dialog.

David

[sylsau]

Check the DBMS your tables are set to.

You can set the default DBMS for an EA model from the main menu via Settings | Database Datatypes. At the top of the dialog you'll see a drop-down of DBMS products. Select yours and check the Set as Default box. [This box will be checked if you've already set this option, but the drop-down does not show the default on entry to the dialog for some reason.]

Note that this setting is specific to each model. You must have a model open to invoke the dialog.

David

First, thanks for your answer.

For my problem, I have selected Oracle as Databse for my current diagram. When I select a table of my model, I can see in the operations part of the table that the table has got some foreign key constraints. These keys are well present on the diagram.

But When I generate the Oracle SQL Script for this table, I have just the primary key constraint that is created in the SQL File !

It's very strange. I don't understand what is the problem.
Someone has an idea ?

[«Midnight»]

How did you create the operations in the EA model? Did you add them yourself, or did you have EA create them from the Foreign Keys dialog?

Foreign keys are best left to EA via creating an association - do not try to define any custom properties for the link - and then selecting Foreign Keys from the context menu when you right-click on the link.

If you add them yourself, you need to make sure to use the stereotypes in the EA dialog.

Also, make sure you create and save the Table element before you add attributes and operations. Doing this flags EA that it should bring up custom dialogs when you add attributes and operations.

David

[sylsau]

How did you create the operations in the EA model? Did you add them yourself, or did you have EA create them from the Foreign Keys dialog?

Foreign keys are best left to EA via creating an association - do not try to define any custom properties for the link - and then selecting Foreign Keys from the context menu when you right-click on the link.

If you add them yourself, you need to make sure to use the stereotypes in the EA dialog.

Also, make sure you create and save the Table element before you add attributes and operations. Doing this flags EA that it should bring up custom dialogs when you add attributes and operations.

David

First, I created my tables and then I created the links between these tables. More clearly, I created dependency between the tables because my model is a logical model of data.

And after that, I've seen that the column of tables that are concerned by the foreign keys constraints are marked with the 2 letters "FK" and the operations part of the tables is completed by the foreigne keys !

So, all seems well created. When I'm going on the operations part of my tables, the stereotype is on FK.

It's strange because the foreign keys constraints are presents ! May be, there is an other way to create these constraints and I don't have use the good ?

[«Midnight»]

Try this on a small test model:

• Create two table elements.
  
• Add appropriate attributes so that you can create a foreign key relationship, remembering to define a primary key for the parent.
  
• Draw an Association link using the appropriate button from the toolbox, from the child to the parent.
  
• Right-click the association and choose Foreign Keys from the context menu.
  
• Associate the fields as and accept the dialog.
  
• Verify that EA has created the key.
  
• Ensure that none of the table elements are currently selected - and that the package is selected in the Project Browser tree.
  
• Generate Package DDL.

Does this have any different effect?

[sylsau]

Try this on a small test model:

• Create two table elements.
• Add appropriate attributes so that you can create a foreign key relationship, remembering to define a primary key for the parent.
• Draw an Association link using the appropriate button from the toolbox, from the child to the parent.
  
• Right-click the association and choose Foreign Keys from the context menu.
• Associate the fields as and accept the dialog.
• Verify that EA has created the key.
• Ensure that none of the table elements are currently selected - and that the package is selected in the Project Browser tree.
  
• Generate Package DDL.

Does this have any different effect?

With this method, the generated code is good ! The SQL contains the foreign key constraint.

In my other model, when I create association between 2 tables, I used to Dependency button for my link. it's strange that the foreign keys be displayed on the diagram zone with this method but no in the generated code.

Now, I have got a big problem since in my model, I have got around 200 tables ! So, is there a simple solution to fix my problem and to transform the keys that I have created ? Or, I must make again all the keys ?

Thanks for your help.

[«Midnight»]

OK. I'm pleased that there is a way out for you. But you're right about this being a lot of work.

Before you trash the entire model, here's a long shot.

[I have not tried this at all, so work with a copy of your model! Better yet, try this on something very small first, then if it works try it on a copy of your full model.]

Try opening the model as a database, and change one of the dependency links to a simple association. You'll need to read the help file - or the user guide, which has the same information - to find the correct table and type name. Remember that the type names are case sensitive. Having made the change, see if you can now generate the DDL. My guess is that the generator is iterating through associations, so never looks at your dependency links.

If this works you can do some kind of mass update via SQL.

In the meantime, zip up one of your small test models and report your current situation as a bug to Sparx, including your model. If EA is displaying the foreign key entries when you use dependencies, it should generate the DDL. IMHO either the generator needs to be fixed, or EA should not allow you to build the keys.

You can reach the bug report via the Support link at the top of any forum page, followed by the Bug Report link in the left side menu. You can also reach it from the EA main menu via Help | On-line Resources | Bug Report Page.

David