Description:
Even if foreign_key_check is set to 0, table fails to be created.
How to repeat:
I have a database and i took up a dump using --no-data. I am trying to import that DB structure in an empty DB.
I am ignoring other queries, following the queries I am trying to execute,
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
set foreign_key_checks=0;
DROP TABLE IF EXISTS `ACCOUNT`;
CREATE TABLE `ACCOUNT` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
...
`CREATED_BY_ID` int(11) DEFAULT NULL,
...
PRIMARY KEY (`ID`),
...
KEY `FK_ACCOUNT_CREATED_BY_idx` (`CREATED_BY_ID`),
...
CONSTRAINT `FK_ACCOUNT_CREATED_BY` FOREIGN KEY (`CREATED_BY_ID`) REFERENCES `USER` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
...
) ENGINE=InnoDB AUTO_INCREMENT=4452 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Above code works as the file has a line saying foreign key check = 0, even though it has reference to table USER which is yet not created.
Now I am trying to do same for other table as following
DROP TABLE IF EXISTS `ACTIVITY`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ACTIVITY` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
...
`ACTIVITY_BY_ID` int(11) DEFAULT NULL,
...
PRIMARY KEY (`ID`),
KEY `FK_ACTIVITY_TO_USER_idx` (`ACTIVITY_BY_ID`),
...
CONSTRAINT `FK_ACTIVITY_TO_USER` FOREIGN KEY (`ACTIVITY_BY_ID`) REFERENCES `USER` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
The above code fails saying Error Code: 1215. Cannot add foreign key constraint
Below is the sql for importing USER table. However this sql is being executed later.
DROP TABLE IF EXISTS `USER`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `USER` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (`ID`),
..
) ENGINE=InnoDB AUTO_INCREMENT=4070 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
If I execute the USER script first and then ACTIVITY, it works, otherwise it does not.
My question is, why does it work for first table ACCOUNT without the parent table and not for second table ACTIVITY?
Things to note,
The second table which gives me error ACTIVITY has only one foreign key.
The fist table which works ACCOUNT has many other unsatisfied (table not yet created) foreign keys, still it works.
https://stackoverflow.com/questions/48239328/mysql-import-saying-error-code-1215-cannot-ad...
