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,
set foreign_key_checks=0;
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,
`ENTITY_TYPE` varchar(45) COLLATE utf8_unicode_ci NOT NULL, -- this
`ACTIVITY_TYPE` varchar(45) CHARACTER SET utf8 NOT NULL, -- this
...
PRIMARY KEY (`ID`),
KEY `FK_ACTIVITY_TO_USER_idx` (`ACTIVITY_BY_ID`),
FULLTEXT KEY `FK_FULL_TEXT_ACTIVITY_TYPE` (`ACTIVITY_TYPE`),
FULLTEXT KEY `FK_FULL_TEXT_ENTITY_TYPE` (`ENTITY_TYPE`),
...
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
It should work as foreign_key_checks is set to 0.
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.
The lines I marked with --this for the table ACTIVITY is causing the issue. If I remove the two lines, code works fine.
Why a fulltext index create a foreign key issue?
