What I am trying to do I've done countless times but now fails having upgraded from MySql 5.6.26 to 5.6.27 (also fails in 5.7.x). I have a database with a bunch of tables with foreign key constraints. I have exported (using phpMyAdmin) replacements tables specifying the following export options:
Disable foreign key checks
Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement
Add CREATE TABLE statement
The resulting export file contains in part:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `application`;
CREATE TABLE `application` (
`PK_Application` int(11) NOT NULL,
`FK_Parent` int(11) DEFAULT NULL,
`ApplicationLevel` tinyint(4) NOT NULL,
`Description` varchar(35) NOT NULL,
`Sequence` tinyint(4) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
. . .
. . .
DROP TABLE IF EXISTS `product_x_application`;
CREATE TABLE `product_x_application` (
`PK_Product_X_Application` int(11) NOT NULL,
`Product_PKID` varchar(36) NOT NULL,
`FK_Application` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
. . .
. . .
ALTER TABLE `product_x_application`
ADD CONSTRAINT `product_x_application_ibfk_2` FOREIGN KEY (`FK_Application`) REFERENCES `application` (`PK_Application`) ON DELETE CASCADE ON UPDATE CASCADE;
When this file is input to either mysql.exe or the phpMyAdmin import function when tables application and product_x_application already exist, the DROP statement for table application succeeds but the subsequent CREATE TABLE statement fails with:
ERROR 1215 (HY000): Cannot add foreign key constraint
If, however, I first drop the product_x_application table, which has a foreign key constraint product_x_application_ibfk_2 that references table application, then the CREATE TABLE statement succeeds.
I have since learned more info. I went up to my production machine, which is running Linux with a MySql 5.5 server and phpMyAdmin 4.4.23 phpMyAdmin, and did an export of just the two tables in question and was able to import the resulting file into my MySql 5.6.27 server. Here are the two complete export files (structure only). First from the problematic 5.6.27 server:
-- phpMyAdmin SQL Dump
-- version 4.4.15
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 10, 2016 at 07:01 AM
-- Server version: 5.6.26-log
-- PHP Version: 5.6.12
SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `spmorell_sami`
--
-- --------------------------------------------------------
--
-- Table structure for table `application`
--
DROP TABLE IF EXISTS `application`;
CREATE TABLE `application` (
`PK_Application` int(11) NOT NULL,
`FK_Parent` int(11) DEFAULT NULL,
`ApplicationLevel` tinyint(4) NOT NULL,
`Description` varchar(35) NOT NULL,
`Sequence` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `product_x_application`
--
DROP TABLE IF EXISTS `product_x_application`;
CREATE TABLE `product_x_application` (
`PK_Product_X_Application` int(11) NOT NULL,
`Product_PKID` varchar(36) NOT NULL,
`FK_Application` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `application`
--
ALTER TABLE `application`
ADD PRIMARY KEY (`PK_Application`);
--
-- Indexes for table `product_x_application`
--
ALTER TABLE `product_x_application`
ADD PRIMARY KEY (`PK_Product_X_Application`),
ADD KEY `Product_PKID` (`Product_PKID`),
ADD KEY `FK_Application` (`FK_Application`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `application`
--
ALTER TABLE `application`
MODIFY `PK_Application` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `product_x_application`
--
ALTER TABLE `product_x_application`
MODIFY `PK_Product_X_Application` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `product_x_application`
--
ALTER TABLE `product_x_application`
ADD CONSTRAINT `product_x_application_ibfk_1` FOREIGN KEY (`Product_PKID`) REFERENCES `product` (`Product_PKID`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `product_x_application_ibfk_2` FOREIGN KEY (`FK_Application`) REFERENCES `application` (`PK_Application`) ON DELETE CASCADE ON UPDATE CASCADE;
SET FOREIGN_KEY_CHECKS=1;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Notice that the primary key for the application table is defined in an ALTER TABLE statement. Here is the exported file form the 5.5 server:
-- phpMyAdmin SQL Dump
-- version 3.5.8.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 10, 2016 at 05:09 AM
-- Server version: 5.5.42-37.1-log
-- PHP Version: 5.4.23
SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `spmorell_sami`
--
-- --------------------------------------------------------
--
-- Table structure for table `application`
--
DROP TABLE IF EXISTS `application`;
CREATE TABLE `application` (
`PK_Application` int(11) NOT NULL AUTO_INCREMENT,
`FK_Parent` int(11) DEFAULT NULL,
`ApplicationLevel` tinyint(4) NOT NULL,
`Description` varchar(35) NOT NULL,
`Sequence` tinyint(4) NOT NULL,
PRIMARY KEY (`PK_Application`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
-- --------------------------------------------------------
--
-- Table structure for table `product_x_application`
--
DROP TABLE IF EXISTS `product_x_application`;
CREATE TABLE `product_x_application` (
`PK_Product_X_Application` int(11) NOT NULL AUTO_INCREMENT,
`Product_PKID` varchar(36) NOT NULL,
`FK_Application` int(11) NOT NULL,
PRIMARY KEY (`PK_Product_X_Application`),
KEY `Product_PKID` (`Product_PKID`),
KEY `FK_Application` (`FK_Application`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1633 ;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `product_x_application`
--
ALTER TABLE `product_x_application`
ADD CONSTRAINT `product_x_application_ibfk_2` FOREIGN KEY (`FK_Application`) REFERENCES `application` (`PK_Application`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `product_x_application_ibfk_1` FOREIGN KEY (`Product_PKID`) REFERENCES `product` (`Product_PKID`) ON DELETE CASCADE ON UPDATE CASCADE;
SET FOREIGN_KEY_CHECKS=1;
Here the primary key for the application table is defined immediately when the table is defined. I believe the problem is that the product_x_application table, which already exists when the system is attempting to re-create the application table, has a foreign key constraint to a table that when re-created does not have the required index on the foreign key because the primary key has not yet been defined.
I have even more info. I had installed a later version of phpMyAdmin (4.5.0.2) but forgot all about it. For giggles I decided to do the export with that. As before, I specified as options ADD DROP TABLE (ADD CREATE TABLE is automatically checked and cannot be unchecked). But now, next to the IF NOT EXISTS sub-option for ADD CREATE TABLE it says in parentheses, "(less efficient as indexes will be generated during table creation)", which of course is precisely what I need (normally I would not think of checking this option because I know the table can't possibly exist at this point). And the output is:
-- phpMyAdmin SQL Dump
-- version 4.5.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 10, 2016 at 07:54 AM
-- Server version: 5.6.26-log
-- PHP Version: 5.6.12
SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `spmorell_sami`
--
-- --------------------------------------------------------
--
-- Table structure for table `application`
--
DROP TABLE IF EXISTS `application`;
CREATE TABLE IF NOT EXISTS `application` (
`PK_Application` int(11) NOT NULL AUTO_INCREMENT,
`FK_Parent` int(11) DEFAULT NULL,
`ApplicationLevel` tinyint(4) NOT NULL,
`Description` varchar(35) NOT NULL,
`Sequence` tinyint(4) NOT NULL,
PRIMARY KEY (`PK_Application`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `product_x_application`
--
DROP TABLE IF EXISTS `product_x_application`;
CREATE TABLE IF NOT EXISTS `product_x_application` (
`PK_Product_X_Application` int(11) NOT NULL AUTO_INCREMENT,
`Product_PKID` varchar(36) NOT NULL,
`FK_Application` int(11) NOT NULL,
PRIMARY KEY (`PK_Product_X_Application`),
KEY `Product_PKID` (`Product_PKID`),
KEY `FK_Application` (`FK_Application`)
) ENGINE=InnoDB AUTO_INCREMENT=1633 DEFAULT CHARSET=latin1;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `product_x_application`
--
ALTER TABLE `product_x_application`
ADD CONSTRAINT `product_x_application_ibfk_1` FOREIGN KEY (`Product_PKID`) REFERENCES `product` (`Product_PKID`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `product_x_application_ibfk_2` FOREIGN KEY (`FK_Application`) REFERENCES `application` (`PK_Application`) ON DELETE CASCADE ON UPDATE CASCADE;
SET FOREIGN_KEY_CHECKS=1;
So the problem all along was the phpMyadmin 4.4.15 release and not the MySql release. Clearly I had been periodically upgrading phpMyAdmin releases and I can infer that I never had the occasion to run the import with the problematic (at least for me) phpMyAdmin release 4.4.15 until now.