FK problem: ibd file does not currently exist

[Richard Gundersen]

Hi

I've got a strange foreign key constraint problem in that when I try to do an insert into a table with some foreign keys, even though those keys reference valid fields in valid tables, I get the "cannot add or update...." [1216] error message.

I ran SHOW INNODB STATUS and got the following:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
050426 19:16:38 Transaction:
TRANSACTION 0 17920, ACTIVE 0 sec, OS thread id 3584 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 1, query id 5 localhost 127.0.0.1 ODBC update
insert into resource_attribute_definition_assoc (resource_id,attribute_definition_id) VALUES (3,1)
Foreign key constraint fails for table `bdb_3829306995174123066/resource_attribute_definition_assoc`:
,
CONSTRAINT `resource_attribute_definition_assoc_ibfk_2` FOREIGN KEY (`attribute_definition_id`) REFERENCES `attribute_definition` (`id`)
Trying to add to index `attribute_definition_index` tuple:
DATA TUPLE: 2 fields;
0: len 3; hex 800001; asc ;; 1: len 3; hex 800003; asc ;;

But the parent table `bdb_3829306995174123066/attribute_definition`
or its .ibd file does not currently exist!
------------

The table in question is defined with this SQL:

----------------------------------------------------
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 this means? Are my indexes broken? I couldn't find any .ibd files under the <mysqlroot>/data directory. I tried a repair on all tables using Administrator with no success.

I've seen the error go away before by recreating the database, which may be fine while I'm in development mode, but when I move to production, I want to understand this problem so I can prevent it happening again.

Really appreciate any help you can give me

Richard