Toad World® Forums

How to do indexes


#1

I am having trouibles with indexes. My generated script results in errors.
In particular when I have a table consisting of two PFKs. TDM generates an index for the primary key and it generates indexes for the foreign key, ending up with an error that an index already exists for those columns.

My 1st attempt)
Create table
Create Foreign keys by adding relationship
Name the relationship FK_TABLE1_TABLE2
Name the index in the relationship tab IDX_TABLE1_TABLE2
Create the primary key PK_TABLE2

resulting script complains when trying to create the index towards the bottom for foreign keys, that an index already exists for those columns.

My 2nd attempt)
Create table
Create Foreign keys by adding relationship
Name the relationship FK_TABLE1_TABLE2
Name the index in the relationship tab IDX_TABLE1_TABLE2
Create the primary key PK_TABLE2
Set “using” textedit to IDX_TABLE2
Create Index IDX_TABLE2 and assign items

same result.

3rd attempt)
Should I just leave the index line blank in the relationship dialog?


#2

Hello,

Indexes automatically generated to relationships are now generated after primary keys. However, you don’t have to generate such indexes - not to generate these “automatic” indexes, clear the Create Index to Foreign Key option in the Relationship Properties dialog. Instead, create such an index manually - as you did it in your second attempt with IDX_TABLE2, including “using” at a primary key.

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

Regards,

Vladka + Mario