Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the two following tables:

survey_main
----------
id
name

answers_main
--------------
id
survey_id

I have the following foreign key constraint on the answers_main table:

CONSTRAINT `fk_answers_main_survey` FOREIGN KEY (`survey_id`) REFERENCES `survey_main` (`id`) ON DELETE CASCADE)

If I try to delete a record from survey_main that has child records in the answers_main table I get the following error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (planet.answers_main, CONSTRAINT fk_answers_main_survey FOREIGN KEY (survey_id) REFERENCES survey_main (id) ON DELETE CASCADE)

I understand what the error is saying, but shouldn't the fact that I have cascading deletes make it so this error would never be thrown? What am I missing here?

UPDATE:

Here is the command I am running and the error it throws, copied directly form the command line:

mysql> delete from survey_main where id = 1750;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`planet`.`answers_main`, CONSTRAINT `fk_answers_main_survey` FOREIGN KEY (`survey_id`) REFERENCES `survey_main` (`id`) ON DELETE CASCADE)

Here is what is showing under last foreign key error when I do SHOW ENGINE INNODB STATUS:

130627  8:43:11 Transaction:
TRANSACTION E7DB6, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
27 lock struct(s), heap size 3112, 19 row lock(s), undo log entries 6
MySQL thread id 1007, OS thread handle 0x10e82a000, query id 56615 localhost root updating
delete from survey_main where id = 1750
Foreign key constraint fails for table `planet`.`answers_main`:
,
  CONSTRAINT `fk_answers_main_survey` FOREIGN KEY (`survey_id`) REFERENCES `survey_main` (`id`) ON DELETE CASCADE
Trying to add in child table, in index `survey_id` tuple:
DATA TUPLE: 6 fields;
 0: len 4; hex 000006f0; asc     ;;
 1: SQL NULL;
 2: len 4; hex 00002fe2; asc   / ;;
 3: len 4; hex 00004a0d; asc   J ;;
 4: len 4; hex 00000c07; asc     ;;
 5: len 4; hex 0004090c; asc     ;;

But in parent table `planet`.`survey_main`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 22; compact format; info bits 32
 0: len 4; hex 000006f0; asc     ;;
 1: len 6; hex 0000000e7db6; asc     } ;;
 2: len 7; hex 020000027a08bc; asc     z  ;;
 3: len 17; hex 53757276657920666f7220746573747321; asc Survey for tests!;;
 4: len 0; hex ; asc ;;
 5: len 1; hex 01; asc  ;;
 6: len 2; hex 0001; asc   ;;
 7: len 4; hex 00000000; asc     ;;
 8: len 4; hex 00000000; asc     ;;
 9: len 4; hex 000006ba; asc     ;;
 10: len 8; hex 8000000000000000; asc         ;;
 11: len 1; hex 80; asc  ;;
 12: SQL NULL;
 13: len 8; hex 8000000000000000; asc         ;;
 14: len 4; hex 00000c07; asc     ;;
 15: len 8; hex 8000124f06e1707a; asc    O  pz;;
 16: len 4; hex 00000c07; asc     ;;
 17: len 8; hex 8000124f06e1707a; asc    O  pz;;
 18: len 1; hex 81; asc  ;;
 19: len 8; hex 0000013f864b33fb; asc    ? K3 ;;
 20: len 8; hex 0000000000000000; asc         ;;
 21: SQL NULL;

UPDATE #2:

Below are the create table statements for both tables.

mysql> show create table survey_main\G
*************************** 1. row ***************************
       Table: survey_main
Create Table: CREATE TABLE `survey_main` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary ID',
  `title` varchar(150) NOT NULL,
  `description` mediumtext NOT NULL,
  `type` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `question_amt` smallint(5) unsigned NOT NULL,
  `parent_survey_id` int(10) unsigned NOT NULL DEFAULT '0',
  `taken_amt` int(10) unsigned NOT NULL DEFAULT '0',
  `latest_campaign_id` int(10) unsigned DEFAULT NULL,
  `last_taken_date` datetime NOT NULL,
  `user_did_finish` tinyint(1) NOT NULL DEFAULT '0',
  `last_user` int(10) unsigned DEFAULT NULL,
  `last_date` datetime NOT NULL,
  `create_user` int(10) unsigned DEFAULT NULL,
  `create_date` datetime NOT NULL,
  `modify_user` int(10) unsigned DEFAULT NULL,
  `modify_date` datetime NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `utccdate` bigint(20) unsigned NOT NULL,
  `utclasttakendate` bigint(20) unsigned NOT NULL,
  `theme_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `create_user` (`create_user`),
  KEY `title` (`title`),
  KEY `latest_campaign_id` (`latest_campaign_id`),
  KEY `fk_survey_main_lastUser` (`last_user`),
  KEY `fk_survey_main_modifyUser` (`modify_user`),
  KEY `fk_survey_main_theme` (`theme_id`),
  CONSTRAINT `fk_survey_main_theme` FOREIGN KEY (`theme_id`) REFERENCES `themes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_survey_main_campaign` FOREIGN KEY (`latest_campaign_id`) REFERENCES `survey_campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_survey_main_createUser` FOREIGN KEY (`create_user`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_survey_main_lastUser` FOREIGN KEY (`last_user`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_survey_main_modifyUser` FOREIGN KEY (`modify_user`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1790 DEFAULT CHARSET=utf8 COMMENT='Main Table For Designed Surveys'
1 row in set (0.00 sec)

mysql> show create table answers_main\G
*************************** 1. row ***************************
       Table: answers_main
Create Table: CREATE TABLE `answers_main` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `survey_id` int(10) unsigned DEFAULT NULL,
  `campaign_id` int(10) unsigned DEFAULT NULL,
  `question_id` int(10) unsigned DEFAULT NULL,
  `participant_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `choice` int(10) unsigned DEFAULT NULL,
  `scale_from` smallint(6) NOT NULL DEFAULT '0',
  `scale_to` smallint(6) NOT NULL DEFAULT '0',
  `date_fillin` datetime NOT NULL,
  `addl_fillin` varchar(150) NOT NULL,
  `left_blank` tinyint(1) NOT NULL DEFAULT '0',
  `answer_date` datetime NOT NULL,
  `utcanswerdate` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `survey_id` (`survey_id`,`campaign_id`,`question_id`,`participant_id`,`user_id`),
  KEY `fk_answers_main_campaign` (`campaign_id`),
  KEY `fk_answers_main_question` (`question_id`),
  KEY `fk_answers_main_participant` (`participant_id`),
  KEY `fk_answers_main_user` (`user_id`),
  CONSTRAINT `fk_answers_main_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_answers_main_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `survey_campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_answers_main_participant` FOREIGN KEY (`participant_id`) REFERENCES `survey_participants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_answers_main_question` FOREIGN KEY (`question_id`) REFERENCES `survey_questions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_answers_main_survey` FOREIGN KEY (`survey_id`) REFERENCES `survey_main` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=264471 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
share|improve this question
    
If you convert those hex values to decimal you can get the details on the row being inserted. Can you show the full schema for the two tables? –  Ike Walker Jun 27 '13 at 19:27
    
Can you add the output of SHOW CREATE TABLE tablename; for both tables? –  ypercube Jun 28 '13 at 5:35
    
@ypercube - yes, take a look at my second update. –  Abe Miessler Jun 28 '13 at 15:29
    
You say the answers table is actually answers_main... but your SHOW ENGINE INNODB STATUS references a table called survey_answers. If that has not been edited, then you have another table in play here. If you did edit it, please capture a fresh one and post it without changes. It's also possible that if you've renamed tables or columns at some point, you could have blown InnoDB's mind, but I've only seen that on older 5.1 releases using built-in InnoDB, which is not "the same InnoDB" that 5.5 has. –  Michael - sqlbot Jun 28 '13 at 18:59
    
Hi @Michael-sqlbot, the sentences under my UPDATE #2 explains the difference in table names. –  Abe Miessler Jun 28 '13 at 19:00
show 3 more comments

1 Answer

On the face of it, that does seem impossible.

The thing is, your error suggests it's not that you're trying to delete at all.

The message you're getting suggests you're trying to insert or update a row in the child table, not delete a row from the parent table. If the foreign key you posted was causing the problem relative to a delete, you should see this message, instead:

Cannot delete or update a parent row: a foreign key constraint fails (`test`.`bar`, CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`))

It's also possible you have some BEFORE DELETE trigger magic on survey_main that's doing something unexpected.

Right after this error occurs, try this:

SHOW ENGINE INNODB STATUS;

The LATEST FOREIGN KEY ERROR section should give you something more to go on. Failing that, you could enable the general log, which will show queries executed by triggers and other stored programs, as well as the queries you're directly executing, to shed light on what might be going on behind the scenes.


Update (#1) Things are definitely not as they seem and the full table definitions are going to be pretty critical, here.

Also, the version of MySQL you're using may also be relevant, so please mention it.

With nothing more to go on at the moment, I'm speculating that you have invalid data in the survey_id column of the survey_answers table. To test that theory:

SELECT *
  FROM survey_answers sa
  LEFT JOIN survey_main sm ON sm.id = sa.survey_id
 WHERE sm.id IS NULL;

If I understand your schema correctly, then this query will return zero rows if I am wrong. :) If you get rows returned, then those rows have survey_answers records that contain an survey_id value that doesn't exist in the id column of survey_main.

share|improve this answer
    
Thanks for the response Michael. I went ahead and updated my question with some more details based on the direction you pointed me. I also double checked and there is no trigger magic happening so that is out. –  Abe Miessler Jun 27 '13 at 15:50
    
Updated the answer. –  Michael - sqlbot Jun 28 '13 at 3:01
    
@AbeMiessler No trigger in either of the 2 tables? –  ypercube Jun 28 '13 at 5:37
    
Hey Michael, I ran your query and no rows returned. I went ahead and included the create table statements in my original question. innodb_version is 1.1.8 and version is 5.5.28 –  Abe Miessler Jun 28 '13 at 15:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.