MySQL Forums
Forum List  »  MySQL Administrator

Foreign key constraints with Administrator or Query Browser on MacOS X
Posted by: Jean-David Lanz
Date: August 22, 2009 09:02AM

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.

Options: ReplyQuote




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.