Frustrating learning curve

Toad Data Modeler
1

Have been Erwin User for almost 20 years

Trying to learn Toad, but now have spent 2 whoe days and don’t even have first table done

  • for unknown reasons cant do certain things
    • cant delete keys
    • cant get Primary key to be non-clustered
    • have no clue how, but now have 3 keys on ibe table and cant do anything with any of them
    • other features for some reason sometimes are not editable in table and indexes
    • cant get it to register ‘extra fields’ to carry along on indexes (want a logical index on name and carry along the ID field as supplemental but not included in index calculation)

basically a screwed up mess on 2 simple tables

Gonna be in the software scrap heap if i don’t figure it out today

Thanks

2

Hi,

thank you for your feedback.

When you create a new table, one empty key is present in the newly created entity. Empty key doesn’t affect generated SQL code, it is just “ready to use” item. When you create other keys, they can be normally deleted (there is no reason why you should have 3 keys with no possibility to delete them). In addition, when you mark an attribute as Unique item, new alternate key will be created automatically. That should be a timesaver.

Items are not editable only in case the same item is already edited (on the same form or via some other GUI.) Many dialogs are modeless, that’s why you need to click Apply button before you continue doing some changes in your model. Example: add several attributes to entity. Click Apply and then start editing them. Reason: you can add attributes to model also via Physical Model Explorer (tree) etc.

I would like to recommend you to create the structure in your physically existing database and reverse engineer it. Or, if you work with databases that supports reverse engineering from SQL script, reverse engineer the script. This should help.

Finally let me note that there are numerous flash movies in the Library section and the product also contains User Guide and Help file where you can find valuable information.

Re clustered key. I do not know what database is your target platform. In model for MS SQL Server 2008, when you edit Primary Key, on the first tab there is check box Clustered. Solution: Edit primary key, uncheck checkbox Clustered.

Regards,

Vaclav

3

Been there done that

  • not only applied, but went clear out of application and got back in - still can’t edit some items
  • marked key as unclustered, then when I mark a different one as clusterd, it automatically makes it a primary key, which is not what I want - basically I go incircles when trying to create a table with ID field as primary key, but another field having a clustered index

Am just going in circles, as am now in day 3 and still on first 2 tables

No, I am creating a new database, so i don’t have a DB to reverse engineer from. But if Toad is only good if you reverse engineer another db that has already been created, then maybe it won’t serve my needs, which are to be able to model and design a new database

Thanks

4

Hi,

I would like to ask you for details. Please let me know:

  • what is your target database (incl. version)
  • what item cannot be edited (provide screenshot, if possible)
  • ideally, if you can, please send me your sample model to modeling@quest.com

Re clustered index: Please read the following article
http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx

You can find there the following:
"...a table can contain only one clustered index..."

"...Note PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint..."

In Toad Data Modeler you can edit Primary Key and uncheck checkbox Clustered. Then edit another key and check the Clustered checkbox. Your model will be OK then. Checking the Clustered checkbox doesn't change primary key in your model automatically. I guess you might do something else. Please feel free to use Verification feature also. Number of clustered keys is verified in MS SQL Server 2008 models.

My recommendation to work with physical database was meant as a tip for you. Sometimes such approach may help, because database can have limitations that designer is not aware of (new funtionality or new restriction added to newer vesion of database.) When you try to run invalid SQL on physically existing database you might find that problem is not in the modeling tool but that some restriction exists.

Screenshot attached.

Regards,

Vaclav

clustered2.png
clustered2.png773×538 27.1 KB

5

Hi,

I would like to ask you for details. Please let me know:

  • what is your target database (incl. version)
  • what item cannot be edited (provide screenshot, if possible)
  • ideally, if you can, please send me your sample model to modeling@quest.com

Re clustered index: Please read the following article
http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx

You can find there the following:
"...a table can contain only one clustered index..."

"...Note PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint..."

In Toad Data Modeler you can edit Primary Key and uncheck checkbox Clustered. Then edit another key and check the Clustered checkbox. Your model will be OK then. Checking the Clustered checkbox doesn't change primary key in your model automatically. I guess you might do something else. Please feel free to use Verification feature also. Number of clustered keys is verified in MS SQL Server 2008 models.

My recommendation to work with physical database was meant as a tip for you. Sometimes such approach may help, because database can have limitations that designer is not aware of (new funtionality or new restriction added to newer vesion of database.) When you try to run invalid SQL on physically existing database you might find that problem is not in the modeling tool but that some restriction exists.

Screenshot attached.

Regards,

Vaclav

clustered1.png
clustered1.png602×541 27.5 KB

6

See how the delete button is not active - no matter what i do, that doesnt change

see how the first key is set as a primary key - I didnt do that - all i wanted for the Index was for it to be clustered, not the primary key - no matter what i do i cant get the combination of a primary key on one field and another field being the clustered key

its an sql server 2008 target

at this point I've eliminated the other table I had started with, so this is the i=only table in the database -

almost 3 complete days now

key error.jpeg
key error.jpeg960×540 64 KB

7

Still fighting it

Have completely started over - same result

  • Toad assumes that if i cluster an index i want that to be the primary key - this is not correct
  • Will not allow me to delete a key - have never yet seen that button activated

Yes, I see the code for creating a table with non-clustered primary key (and I am familiar with the DDL code), but if I have to hand write the SQL it sort of defeats the purpose of having the software.

It should be do-able in the Toad screens. So far I haven’t even tried to do anything complicated or corner-case, where I would expect to have to supplement the app with some hand written code, but just defining a table, fields and indexes and keys on it should not be causing issues

Thanks

gtabetnj

8

Hi,

thank you for sending me additional infomation.
Let me divide my answer to two items:

A) Keys: please edit the attribute and make sure the Unique checkbox is not checked. PK should not be marked as Unique. Unique item creates new alternate key that cannot be removed on tab Keys. If you wish to remove such key, edit the attribute and uncheck checkbox Unique. Thank you.

B) Clustered index: Toad Data Modeler doesn’t assume that if you cluster index that you want it to be primary key. Toad Data Modeler allows you to specify as many clustered keys as you wish. The state is incorrect and verification will warn you about it. Also, it is not necessary to hand-write SQL. The screenshot I sent in my previous answer shows SQL preview of edited entity.

Please find attached two models.

  1. Clustered-keys-wrong.txp
    This model contains definition of PK and two clustered keys. Edit each key (edit Entity, click tab Keys, select key and click Edit) and see checkbox Clustered on tab General. You will see that in Toad Data Modeler you can specify this option via single checkbox. As next step, please run verification on that model. And finally, please edit the entity and click tab SQL preview to see output (or press F9 and generate SQL code). This model shows that you don’t need to write SQL code manually.

  2. Clustered-keys-correct.txp
    PK defined as NONCLUSTERED, one key defined as clustered. Verification outputs no errors, SQL preview shows correct code. This just shows that you can model correct situation in the tool.

Regards,

Vaclav
Message was edited by: vaclav
Clustered-keys-wrong.txp (31.1 KB)

9

Hi,

thank you for sending me additional infomation.
Let me divide my answer to two items:

A) Keys: please edit the attribute and make sure the Unique checkbox is not checked. PK should not be marked as Unique. Unique item creates new alternate key that cannot be removed on tab Keys. If you wish to remove such key, edit the attribute and uncheck checkbox Unique. Thank you.

B) Clustered index: Toad Data Modeler doesn’t assume that if you cluster index that you want it to be primary key. Toad Data Modeler allows you to specify as many clustered keys as you wish. The state is incorrect and verification will warn you about it. Also, it is not necessary to hand-write SQL. The screenshot I sent in my previous answer shows SQL preview of edited entity.

Please find attached two models.

  1. Clustered-keys-wrong.txp
    This model contains definition of PK and two clustered keys. Edit each key (edit Entity, click tab Keys, select key and click Edit) and see checkbox Clustered on tab General. You will see that in Toad Data Modeler you can specify this option via single checkbox. As next step, please run verification on that model. And finally, please edit the entity and click tab SQL preview to see output (or press F9 and generate SQL code). This model shows that you don’t need to write SQL code manually.

  2. Clustered-keys-correct.txp
    PK defined as NONCLUSTERED, one key defined as clustered. Verification outputs no errors, SQL preview shows correct code. This just shows that you can model correct situation in the tool.

Regards,

Vaclav
Message was edited by: vaclav
Clustered-keys-correct.txp (31.1 KB)