MySQL Newbie

I am a db newbie – no, probably worse than that, I am a db troglodyte. I remember card catalogues in the library, and I was OK with them. During my nearly decade programming, I’ve pretty much always managed to find someone else to code any db access / schemas etc. So when I hit a problem I google. It’s not helpful when I don’t find anything. So possibly, this is just such an obvious MySQL basic 101 issue that it doesn’t need to be explained, but I’m putting here anyway.

I’m using MySQL Workbench and forward engineering a db schema that my kind husband helped me put together for a short contract I’m involved with. I happily connect to my local instance of the server, and tell it to execute my file.

Ooops. Here’s the offending line of code:
“CREATE INDEX `scenario_id` ON `mydb`.`question` (`scenario_id` ASC)”
And here’s the error:
Error Code: 1280. Incorrect index name ‘scenario_id’

So it seems that when you create a unique index then MySQL Workbench happily bangs “_UNIQUE” on the end of your index name (which it generated automatically), but if it is not unique, then nothing is put on the end of your index name, which is then the same as the column name, and MySQL didn’t like to execute that. So I just banged an _INDEX onto the index name and then it was happy. (It seems that any suffix will do e.g _PUTSOMETHINGHERE also works .)

So this code is fine.
CREATE INDEX `scenario_id_INDEX` ON `mydb`.`question` (`scenario_id` ASC)

Maybe it’s just a funny with the MySQL Workbench. Please refrain from telling me I’m a db troglodyte. I know this – that’s precisely why I’m using a pretty interface and not writing SQL queries by hand.

Advertisement