Foreign key constraints with Administrator or Query Browser on MacOS X
Hello,
I've been using Oracle or SQLServer for years as a developer, but only a little bit of MySQL until recently. More to the point, I tried to create a database with MySQL Administrator and add tables, which worked like a charm. I used the InnoDB engine for most tables, for the row-level locks. It was when I tried to introduce foreign key constraints that trouble started.
At first glance, everything went OK: I created the constraint, selected the foreign table, the columns and it went all smoothly. (Except when I tried to use it with MyISAM tables and InnoDB ones, but I figured from the documentation I was making a mistake there.)
And then I decided I had to add a field in the primary key of a table that featured in lots of foreign key constraints. So I opened the concerned tables again - and the foreign key constraints had vanished. Only a key remained in the "Indices" tab, with the foreign key name I had given, and the local table fields I had selected. But no more foreign key constraint.
That's number one: what may I have done wrong?
Number two comes in right after that. I sighed at having to rebuild all those keys in a GUI, so I started MySQL Query Browser and noticed I could export SQL code for table definitions. Which I did. Then I added the foreign key constraints definitions into the mix, added the necessary DROP TABLE statements in the beginning, and launched it.
When I finally ironed out my last syntax error, there remained a perfectly (to my eyes) correct statement that, when executed in a script, stubbornly raised an error :
ALTER TABLE `Animal_Party`.`habitat`
ADD CONSTRAINT `fk_dom` FOREIGN KEY (`dom_id`) REFERENCES `dom_lib` (`dom_id`),
ADD CONSTRAINT `fk_lib` FOREIGN KEY (`dom_id`, `lib_id`) REFERENCES `libelle` (`dom_id`, `lib_id`);
I'll hasten to add that yes, tables habitat, dom_lib and libelle all have the necessary fields. The error message was the following one :
Error: Can't create table 'animal_party.#sql-89_8' (errno: 121) (1005)
The number "89_8" varies according to various factors I'm not privy to. The error code 1005 means a table cannot be created, which seems quite correct except I'm not asking for a table to be created. (Or am I? That's number two.)
Number three: WHAT CAN I DO?
Ahem, excuse me for yelling, I've been working on this for most of the day and a goodly portion of the evening and that's on my time off from work, to build a game for my beloved cute daughter and her friends. (And lots of other people if everything goes luckily well, but one step at a time, thank you very much.)
Even without being a lazy sod about those foreign key constraints, I want to be able to rebuild my database definitions from a script in case something goes horribly wrong. For now I'll settle without the constraints, but I'd still like to know.