Can't insert new record because of foreign keys

[Richard Gundersen]

Hi

My database has started acting really strange. I've got an association table 'ASSOC' to link two other tables 'A' and 'B' together. ASSOC therefore has two foreign keys to the other two tables.

A row exists in table A where A.id = 3
A row exists in table B where B.id = 1

And ASSOC has no rows at all. But when I try to insert a row into ASSOC with the ids 3,1, I get the error message "Cannot add or update a child row: a foreign key constraint fails" [1216]

Why? The referenced fields exist - I can do e.g. "SELECT * FROM A WHERE id = 3" and "SELECT * FROM B WHERE id = 1" and I get a row back for each.

I've simplified my example for the sake of trying to explain my problem clearly, but in real life ASSOC is actually defined as follows:

CREATE TABLE resource_attribute_definition_assoc (
resource_id mediumint(9) NOT NULL,
attribute_definition_id mediumint(9) NOT NULL,
KEY resource_index (resource_id),
KEY attribute_definition_index (attribute_definition_id),
PRIMARY KEY (resource_id, attribute_definition_id),
FOREIGN KEY (`resource_id`) REFERENCES `resource` (`id`),
FOREIGN KEY (`attribute_definition_id`) REFERENCES `attribute_definition` (`id`))
TYPE=InnoDB;

Please could someone tell me what I'm doing wrong? I've tried all the things mentioned in the docs but I just don't understand why it's doing this - the referenced fields exist!

Thanks!