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.