node.jsherokumariadbmysql-workbenchadminer

I can't import my SQL tables from Adminer to MySQL Workbench without getting error: ERROR 1215 (HY000) at line 9: Cannot add foreign key constraint

I'm trying to deploy my NodeJS app to Heroku, and exported my MariaDB SQL tables with these parameters:

Format: SQL
Tables: DROP+CREATE |Triggers

However, when I import them I get the vague error: ERROR 1215 (HY000) at line 9: Cannot add foreign key constraint. This is a section of my SQL export:

-- Adminer 4.6.3 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `Govt_answers`;
CREATE TABLE `Govt_answers` (
  `Answer_id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `Question_id` int(2) unsigned NOT NULL,
  `Correct` bit(1) NOT NULL,
  `Answer_prompt` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  PRIMARY KEY (`Answer_id`),
  KEY `Question_id` (`Question_id`),
  CONSTRAINT `Govt_answers_ibfk_1` FOREIGN KEY (`Question_id`) REFERENCES `Govt_questions` (`Question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `Govt_choice_response`;
CREATE TABLE `Govt_choice_response` (
  `Quiz_id` int(2) unsigned NOT NULL,
  `Question_id` int(2) unsigned NOT NULL,
  `Answer_id` int(3) unsigned NOT NULL,
  `Correct` bit(1) DEFAULT NULL,
  `Time_start` datetime DEFAULT NULL,
  `Time_stop` datetime DEFAULT NULL,
  `Student_id` int(4) unsigned NOT NULL,
  KEY `Quiz_id` (`Quiz_id`),
  KEY `Question_id` (`Question_id`),
  KEY `Answer_id` (`Answer_id`),
  KEY `Student_id` (`Student_id`),
  CONSTRAINT `Govt_choice_response_ibfk_1` FOREIGN KEY (`Answer_id`) REFERENCES `Govt_answers` (`Answer_id`),
  CONSTRAINT `Govt_choice_response_ibfk_2` FOREIGN KEY (`Question_id`) REFERENCES `Govt_questions` (`Question_id`),
  CONSTRAINT `Govt_choice_response_ibfk_4` FOREIGN KEY (`Quiz_id`) REFERENCES `Govt_quizes` (`Quiz_id`),
  CONSTRAINT `Govt_choice_response_ibfk_5` FOREIGN KEY (`Question_id`) REFERENCES `Govt_questions` (`Question_id`),
  CONSTRAINT `Govt_choice_response_ibfk_6` FOREIGN KEY (`Answer_id`) REFERENCES `Govt_answers` (`Answer_id`),
  CONSTRAINT `Govt_choice_response_ibfk_7` FOREIGN KEY (`Student_id`) REFERENCES `Govt_profiles` (`Student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
....etc

I tried the SHOW ENGINE INNODB STATUS on my external server running on my Raspberry Pi, but it did not provide any insight on the foreign key issue. Any ideas what the issue is?

Solution

  • I resolved this by disabling foreign key checks and then enabling afterwards. This post has more information.