MySQL Forums
Forum List  »  MySQL Workbench

Re: help: indexes + FOREIGN
Posted by: Sergei Tkachenko
Date: July 09, 2008 04:10AM

This is a bug in editor. It will be fixed in forthcoming version. Until that time if dropping of invalid fk indices is acceptable workaround for you then here is a help Lua script. You can run it in GRT shell (press Ctrl-F3 from Workbench to run it). You can also change the script depending on your needs if you are familiar with Lua scripting language or ask here for changes otherwise.
In the last line you can see an example of script usage, just give it names of schema, table, and invalid fk index to specify the range for processing (live corespoding param empty to process all schemata/tables/indices). FK index is deemed to be invalid if it doesn't reference any column. Script prints the list of removed indices.
Phorum messes up formatting, so here is also external link: http://pasteall.org/1442/lua

Let us know if it helped you or not.

function dropInvalidFKIndices(schema_name, table_name, index_name)
local l, cat, schema, table, index, indices, ind_count

cat = grtV.getGlobal("/wb/doc/physicalModels/0/catalog")
for i = 1, grtV.getn(cat.schemata) do
schema = cat.schemata
if (schema.name == schema_name or schema_name == "") then
for j = 1, grtV.getn(schema.tables) do
table = schema.tables[j]
if (table.name == table_name or table_name == "") then
indices = table.indices
ind_count = grtV.getn(indices)
for k = 1, ind_count do
l = ind_count - k + 1
index = indices[l]
if (index.name == index_name or index_name == "") then
if index.indexType == "FOREIGN" then
if (grtV.getn(index.columns) == 0) then
print ("removing fk index: `" .. schema.name .. "`.`" .. table.name .. "`.`" .. index.name .. "`")
grtV.remove(indices, l)
end
end
end
end
end
end
end
end
end

dropInvalidFKIndices("mydb", "table1", "broken_fk_index") -- use empty string to denote all schemata/tables/indices correspondingly



Edited 3 time(s). Last edit at 07/09/2008 05:02AM by Sergei Tkachenko.

Options: ReplyQuote


Subject
Views
Written By
Posted
3094
July 04, 2008 06:32AM
Re: help: indexes + FOREIGN
2324
July 09, 2008 04:10AM
2007
July 11, 2008 04:18AM


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.