MySQL Forums
Forum List  »  MySQL Administrator

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

Very interesting development here. I'll confess my first thought was to retort, 'Yeah, but there wasn't any data in that table then, nor now'. Which is quite true; so it may mean the creation of the constraint itself violated something in the structure tables. (Still with me here? The constraint is in a table, right?)

And I remembered that there were keys with the same name as the constraints. Maybe the duplicate is on that name?

I'm looking into it. The easiest way seemed to be to drop the keys and try it again. Keys dropped, the message is still the same (I won't bore you with what new number it sports now).

Here are the parts of the code I use for two tables:

/.../

DROP TABLE `Animal_Party`.`etat_hab`;

DROP TABLE `Animal_Party`.`espece`;

/.../

CREATE TABLE `Animal_Party`.`espece` (
`esp_id` varchar(5) NOT NULL,
`dom_id` varchar(5) DEFAULT NULL,
`lib_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`esp_id`),
KEY `fk_dom` (`dom_id`),
KEY `fk_lib` (`dom_id`,`lib_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Animal_Party`.`etat_hab` (
`et_h_id` varchar(5) NOT NULL,
`dom_id` varchar(5) NOT NULL,
`lib_id` bigint(20) NOT NULL,
PRIMARY KEY (`et_h_id`),
KEY `fk_dom` (`dom_id`),
KEY `fk_lib` (`dom_id`,`lib_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/.../

ALTER TABLE `Animal_Party`.`espece`
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`);

ALTER TABLE `Animal_Party`.`etat_hab`
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`);

/.../

You'll have guessed that the statements on table "espece" comes through quite nicely, whereas the last one on table "etat_hab" fails. The only difference I see is that my fields can be NULL in "espece" and Not in "etat_hab", but I modified that and got the same result.

I even dropped the table "etat_hab" and tried to re-create it without the keys "fk_dom" and "fk_lib", with the same result.

This is getting a bit disquieting.

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.