MySQL Forums
Forum List  »  MySQL Workbench

Multiple column foreign key index
Posted by: Ken Gillett
Date: June 15, 2019 05:33AM

I have 2 Foreign Keys in a table, but need a multi-column index on both to enable rapid searching where both FKs are known. So one FK index can be on one of the FKs and then I'd need another index on both FKs with the common FK the second column. In this way, index searches can be performed on each FK and also on both. But...

Although MySQL itself seems to have no problem with this, WB will not allow you to do it directly. Once an index exists for a FK, WB will not allow the selection of any additional column. This appears to be a coded restriction in WB's UI.

To get around this, I could edit the SQL generated for a Forward Engineer process, but that would mean the model and actual MySQL Server's schema would be out of step so any future synchronising would try to correct this, to the detriment of what I had achieved.

My solution was to delete the FK that needed the multi-column index and that of course also deleted its original index. I then created the multi-column index I needed and then re-created the FK, at which point WB seemed happy to use the multi-column index already created.

So, there seems to be an unnecessary restriction in WB, preventing the direct selection of a second column in a FK index. Is there any other way to avoid/get around this? Is there some setting I am not aware of that will 'allow creation of multi-column FK indexes'?

Anyone else come across this problem?

Options: ReplyQuote

Written By
Multiple column foreign key index
June 15, 2019 05:33AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.