StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I have never heard of a case where InnoDB foreign keys work intermittently and need to be dropped and recreated. And I don't recommend you adopt a habit of running unnecessary ALTER TABLE statements.</p> <p>The more likely explanation is that you first tried to insert a row to <code>MealItem</code> before the referenced rows in either <code>FoodItem</code> or <code>Meal</code> have been inserted.</p> <p>You can find more information on the exact foreign key constraint that was violated, by running:</p> <pre><code>mysql> pager less mysql> SHOW ENGINE INNODB STATUS; </code></pre> <p>Look for the subsection something like the following:</p> <pre><code>------------------------ LATEST FOREIGN KEY ERROR ------------------------ ...some information about the transaction and thread context... insert into MealItem (foodItem_id, meal_id, quantity) values(150, 277, 0.375) Foreign key constraint fails for table `mydatabase/MealTime`: CONSTRAINT `FKC92ACD965FF39405` FOREIGN KEY (`foodItem_id`) REFERENCES `FoodItem` (`id`), Trying to add in child table, in index `foodItem_id` tuple: DATA TUPLE: 2 fields; ...some binary description of the row you tried to insert... But in parent table `mydatabase/FoodItem`, in index `PRIMARY`,the closest match we can find is record: ...some binary description of MySQL's guess at a row near the one that's missing... </code></pre> <p>I've mocked up this example, and omitted some of the less useful stuff. This diagnostic information is relatively easy to read (compared to some). From this you can discover exactly which constraint had a conflict, and which parent table is missing the needed row.</p> <hr> <p>Re your comment:</p> <p>You wouldn't happen to be using OS X and MySQL between 5.5.8 and 5.5.12? I found the error you mention in reference to a bug that appeared in a specific version of MySQL, and only on OS X. Details here: <a href="http://bugs.mysql.com/bug.php?id=60309" rel="nofollow">http://bugs.mysql.com/bug.php?id=60309</a></p> <p>The bug should be fixed if you upgrade to at least 5.5.13, or preferably upgrade to the latest<br> (5.5.32 as of June 2013).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload