Toad World® Forums

Default (Disallow Duplicate Key and Index Names): On?


#1

Is there a way to have this set to On/True/Checked by default? Oracle 10G does not allow you to have duplicate table indexes on multiple tables in a schema.

… actually, does other oracle versions allow this?

… As I go through this, whenever I make an Index, Toad appends a _0 after the name. Why have that there when that _# is never updated? For example, on a table called “Disability”, I have an index called “idx_disability”. Since TDM3 changes it to the following, “idx_disability_0”. What is the _0 for? I mistakenly assumed that, the _# is to prevent duplicate index names in a model, but that isnt the case in TDM3.

Message was edited by: shannara@alaska.gov


#2

Hello,

Is there a way to have this set to On/True/Checked by default? Oracle 10G does not allow you to have duplicate table indexes on multiple tables in a schema.

I"m not sure if I understand well, do you mean duplicate index names? If so, please see the Model menu | Verify Model - the options are selected by default.

whenever I make an Index, Toad appends a _0 after the name. Why have that there when that _# is never updated?

This bug was fixed. Please see current Beta. Indexes are displayed this way:
Index1 (IX1), Index2 (IX2) etc.

What is it for?
Please see the attached screenshot.
Attribute 1 (IX1)
Attribute 2 (IX1,IX2, IX3)

No matter if indexes are displayed (Format | Entity tab | Display Indexes), you can see which attributes belong to which indexes.

If you have any questions, please write me back. Thanks.

Vladka
indexes.png


#3

I think I didnt explain myself, or I am not understanding :slight_smile:

Say we have Table A, it has an attribute called, MemberID. And say we have a table B, it has an attribute called MemberID. I create indexes on both tables called, “IDX_MEMBER.ID”. Toad will change the index name to “IDX_MEMBER_ID_0” on both tables. I dont mind that at all :slight_smile:

What gets me is, why attach a _0 at the end? I guess my question is really two questions. 1, I know oracle does not allow duplicate index names in the same schema. I also know that under the Verify Model, there is an option to detect duplicate index names. No problems so far :slight_smile:

Ah, So I suppose my question is … Does the increasing of the # only exist on a per table bases, or does that span the whole model? In my table A and table B above, would table B have IDX_MEMBER_ID_1 automatically?


#4

Hello,

Say we have Table A, it has an attribute called, MemberID. And say we have a table B, it has an attribute called MemberID. I create indexes on both tables called, “IDX_MEMBER_ID”.

As soon as you create an index of the same name, a warning message appears in Message Explorer. See the screenshot with the message.

Toad will change the index name to “IDX_MEMBER_ID_0” on both tables.
TDM will not change an index name (see the index properties, the name is still the same).
If you display the indexes in Workspace, you will see an index name + extension. This extension is just a legend or caption that marks particular index.
“_0” has been replaced with “(IX1)”, “(IX2)” etc., which is available in current Beta.
In your example, the index will be displayed this way:
“IDX_MEMBER_ID (IX1)”.

See the index extension next to particular attribute. - This information says in which index particular attribute is.
Member_ID (IX1)
In other words, it says that Member_ID attribute has been assigned to index one - IX1 (and IX1 = “IDX_MEMBER_ID (IX1)” as we can see directly in WS provided that ‘Display Indexes’ option is selected in Format dialog.)

Please see the screenshot of entity.

To sum up: TDM allows you to create indexes of the same name, however you are notified about this directly via a message in Message Explorer and then also during the model verification.
Names of indexes are not changed automatically, just an extension is added. This extension is just a legend that refers to information on attribute versus index - it just says that particular attribute has been assigned to particular index.

I believe it is much clearer now.

Regards,

Vladka
index1.png


#5

Hello,

Say we have Table A, it has an attribute called, MemberID. And say we have a table B, it has an attribute called MemberID. I create indexes on both tables called, “IDX_MEMBER_ID”.

As soon as you create an index of the same name, a warning message appears in Message Explorer. See the screenshot with the message.

Toad will change the index name to “IDX_MEMBER_ID_0” on both tables.
TDM will not change an index name (see the index properties, the name is still the same).
If you display the indexes in Workspace, you will see an index name + extension. This extension is just a legend or caption that marks particular index.
“_0” has been replaced with “(IX1)”, “(IX2)” etc., which is available in current Beta.
In your example, the index will be displayed this way:
“IDX_MEMBER_ID (IX1)”.

See the index extension next to particular attribute. - This information says in which index particular attribute is.
Member_ID (IX1)
In other words, it says that Member_ID attribute has been assigned to index one - IX1 (and IX1 = “IDX_MEMBER_ID (IX1)” as we can see directly in WS provided that ‘Display Indexes’ option is selected in Format dialog.)

Please see the screenshot of entity.

To sum up: TDM allows you to create indexes of the same name, however you are notified about this directly via a message in Message Explorer and then also during the model verification.
Names of indexes are not changed automatically, just an extension is added. This extension is just a legend that refers to information on attribute versus index - it just says that particular attribute has been assigned to particular index.

I believe it is much clearer now.

Regards,

Vladka
index2.png


#6

That is awesome. The feature that shows the messages in the Message Explorer doesn’t exist in the current public version. Is it safe for me to assume that this feature is in the beta?

I cannot try the beta here at work unfortunately.


#7

As soon as you create an index of the same name, a warning message appears in Message Explorer. See the screenshot with the message.

Sorry, I made a mistake. The warning appears in Message Explorer after you run model verification (and also in Log area of the Model Verification dialog).
It works the same way in current public version and beta too.

Regards,

Vladka