#1215 - Cannot add foreign key constraint

Question

I am trying to migrate my osclass installation to another server. I have copied all files and created a new database. When trying to import my database from backup, I get "#1215 - Cannot add foreign key constraint".

It shows that this bit is a problem:

    --
    -- Table structure for table `oc_t_user`
    --

    CREATE TABLE IF NOT EXISTS `oc_t_user` (
      `pk_i_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `dt_reg_date` datetime NOT NULL,
      `dt_mod_date` datetime DEFAULT NULL,
      `s_name` varchar(100) NOT NULL,
      `s_username` varchar(100) NOT NULL,
      `s_password` char(60) NOT NULL,
      `s_secret` varchar(40) DEFAULT NULL,
      `s_email` varchar(100) NOT NULL,
      `s_website` varchar(100) DEFAULT NULL,
      `s_phone_land` varchar(45) DEFAULT NULL,
      `s_phone_mobile` varchar(45) DEFAULT NULL,
      `b_enabled` tinyint(1) NOT NULL DEFAULT '1',
      `b_active` tinyint(1) NOT NULL DEFAULT '0',
      `s_pass_code` varchar(100) DEFAULT NULL,
      `s_pass_date` datetime DEFAULT NULL,
      `s_pass_ip` varchar(15) DEFAULT NULL,
      `fk_c_country_code` char(2) DEFAULT NULL,
      `s_country` varchar(40) DEFAULT NULL,
      `s_address` varchar(100) DEFAULT NULL,
      `s_zip` varchar(15) DEFAULT NULL,
      `fk_i_region_id` int(10) unsigned DEFAULT NULL,
      `s_region` varchar(100) DEFAULT NULL,
      `fk_i_city_id` int(10) unsigned DEFAULT NULL,
      `s_city` varchar(100) DEFAULT NULL,
      `fk_i_city_area_id` int(10) unsigned DEFAULT NULL,
      `s_city_area` varchar(200) DEFAULT NULL,
      `d_coord_lat` decimal(10,6) DEFAULT NULL,
      `d_coord_long` decimal(10,6) DEFAULT NULL,
      `b_company` tinyint(1) NOT NULL DEFAULT '0',
      `i_items` int(10) unsigned DEFAULT '0',
      `i_comments` int(10) unsigned DEFAULT '0',
      `dt_access_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `s_access_ip` varchar(15) NOT NULL DEFAULT '',
      PRIMARY KEY (`pk_i_id`),
      UNIQUE KEY `s_email` (`s_email`),
      KEY `idx_s_name` (`s_name`(6)),
      KEY `idx_s_username` (`s_username`),
      KEY `fk_c_country_code` (`fk_c_country_code`),
      KEY `fk_i_region_id` (`fk_i_region_id`),
      KEY `fk_i_city_id` (`fk_i_city_id`),
      KEY `fk_i_city_area_id` (`fk_i_city_area_id`),
      CONSTRAINT `oc_t_user_ibfk_1` FOREIGN KEY (`fk_c_country_code`)         REFERENCES `oc_t_country` (`pk_c_code`),
      CONSTRAINT `oc_t_user_ibfk_2` FOREIGN KEY (`fk_i_region_id`) REFERENCES `oc_t_region` (`pk_i_id`),
      CONSTRAINT `oc_t_user_ibfk_3` FOREIGN KEY (`fk_i_city_id`) REFERENCES `oc_t_city` (`pk_i_id`),
      CONSTRAINT `oc_t_user_ibfk_4` FOREIGN KEY (`fk_i_city_area_id`) REFERENCES `oc_t_city_area` (`pk_i_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

Please help.

do the 4 tables at the bottom to which there is a foreign key exist? If so do all oc_t_user_ibfk_#'s in oc_t_user exist in the parent table? I'm guessing you may have some data quality issues. in that one of the 4 fields doesn't have a record in the parent table, or the parent tables haven't been created when this table is attempted to be created. are the referenced keys of the same data type? https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint. You can't import this table until those on which it depends have been created! — xQbert, May 30 '17 at 20:02

developer_hatch · Answer 1 · 2017-05-31T02:50:25.740

You must create first the tables you want to reference, for example, here is one foreing key that reference one table, also the type of the key must match, follow this example an add the rest of your tables, I will show a minimum example that works in the creation of the tables, you must add the correct data of course:

CREATE TABLE IF NOT EXISTS `oc_t_country` (
    `pk_c_code` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`pk_c_code`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `oc_t_region` (
    `pk_i_id_region` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`pk_i_id_region`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `oc_t_city` (
    `pk_i_id_city` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`pk_i_id_city`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `oc_t_city_area` (
    `pk_i_id_area` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`pk_i_id_area`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `oc_t_user` (
  `pk_i_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dt_reg_date` datetime NOT NULL,
  `dt_mod_date` datetime DEFAULT NULL,
  `s_name` varchar(100) NOT NULL,
  `s_username` varchar(100) NOT NULL,
  `s_password` char(60) NOT NULL,
  `s_secret` varchar(40) DEFAULT NULL,
  `s_email` varchar(100) NOT NULL,
  `s_website` varchar(100) DEFAULT NULL,
  `s_phone_land` varchar(45) DEFAULT NULL,
  `s_phone_mobile` varchar(45) DEFAULT NULL,
  `b_enabled` tinyint(1) NOT NULL DEFAULT '1',
  `b_active` tinyint(1) NOT NULL DEFAULT '0',
  `s_pass_code` varchar(100) DEFAULT NULL,
  `s_pass_date` datetime DEFAULT NULL,
  `s_pass_ip` varchar(15) DEFAULT NULL,
  `fk_c_country_code` int(10) unsigned DEFAULT NULL,
  `s_country` varchar(40) DEFAULT NULL,
  `s_address` varchar(100) DEFAULT NULL,
  `s_zip` varchar(15) DEFAULT NULL,
  `fk_i_region_id` int(10) unsigned DEFAULT NULL,
  `s_region` varchar(100) DEFAULT NULL,
  `fk_i_city_id` int(10) unsigned DEFAULT NULL,
  `s_city` varchar(100) DEFAULT NULL,
  `fk_i_city_area_id` int(10) unsigned DEFAULT NULL,
  `s_city_area` varchar(200) DEFAULT NULL,
  `d_coord_lat` decimal(10,6) DEFAULT NULL,
  `d_coord_long` decimal(10,6) DEFAULT NULL,
  `b_company` tinyint(1) NOT NULL DEFAULT '0',
  `i_items` int(10) unsigned DEFAULT '0',
  `i_comments` int(10) unsigned DEFAULT '0',
  `dt_access_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `s_access_ip` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`pk_i_id`),
  UNIQUE KEY `s_email` (`s_email`),
  KEY `idx_s_name` (`s_name`(6)),
  KEY `idx_s_username` (`s_username`),
  KEY `fk_c_country_code` (`fk_c_country_code`),
  KEY `fk_i_region_id` (`fk_i_region_id`),
  KEY `fk_i_city_id` (`fk_i_city_id`),
  KEY `fk_i_city_area_id` (`fk_i_city_area_id`),
  CONSTRAINT `oc_t_user_ibfk_1` FOREIGN KEY (`fk_c_country_code`) REFERENCES `oc_t_country` (`pk_c_code`),
  CONSTRAINT `oc_t_user_ibfk_2` FOREIGN KEY (`fk_i_region_id`) REFERENCES `oc_t_region` (`pk_i_id_region`),
  CONSTRAINT `oc_t_user_ibfk_3` FOREIGN KEY (`fk_i_city_id`) REFERENCES `oc_t_city` (`pk_i_id_city`),
  CONSTRAINT `oc_t_user_ibfk_4` FOREIGN KEY (`fk_i_city_area_id`) REFERENCES `oc_t_city_area` (`pk_i_id_area`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

I get some errors when I run that query: Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. ERROR: Unknown Punctuation String @ 743 STR: // MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//must have the same type of the other table `s_country` varchar(40) DEFAULT ' at line 18 — AlexRns, May 30 '17 at 21:15
@AlexRns rare, I tried and worked just fine, did you erased the comments? — developer_hatch, May 30 '17 at 21:17
Sorry, I did it this time, but got this now: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 43 — AlexRns, May 30 '17 at 21:25
@AlexRns I updated the answer, so you can check the correct sql, I tryed and worked just fine, I eresed the comment and added what was missing — developer_hatch, May 31 '17 at 02:24

#1215 - Cannot add foreign key constraint

1 Answers1