Update of db yields "Error Code: 1215. Cannot add foreign key constraint"

Question

A phpmyadmin export of a remote MySQL db is used to update a local instance of the db. Disable foreign key checks is selected. Yet running the export either as a phpmyadmin import or as a script in Workbench results in

Error Code: 1215. Cannot add foreign key constraint

Background: the schema was created by Doctrine in a Symfony 2.8 application.

The import errors out on:

CREATE TABLE `center` (
 `id` int(11) NOT NULL,
 `center` varchar(20) DEFAULT NULL,
 `county_id` int(11) DEFAULT NULL,
 `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A standalone script that demonstrates the error, copied out of the export, is:

Edit:

If the following script is used in a db with no tables it will not error out if run 2 or more times. Only in the context of the full db will errors be thrown.

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 */;
--
-- Table structure for table `center`
--
DROP TABLE IF EXISTS `center`;
CREATE TABLE `center` (
 `id` int(11) NOT NULL,
 `center` varchar(20) DEFAULT NULL,
 `county_id` int(11) DEFAULT NULL,
 `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- RELATIONS FOR TABLE `center`:
-- `county_id`
-- `county` -> `id`
--
--
-- Dumping data for table `center`
--
INSERT INTO `center` (`id`, `center`, `county_id`, `enabled`) VALUES
(1, 'Tahoe City', 1, 1),
(2, 'Squaw Valley', 1, 0),
(3, 'Kings Beach', 1, 1),
(4, 'Soda Springs', 1, 0),
(5, 'Truckee', 2, 1),
(6, 'Incline Village', 3, 1),
(7, 'N/A', 8, 0);
--
-- Table structure for table `county`
--
DROP TABLE IF EXISTS `county`;
CREATE TABLE `county` (
 `id` int(11) NOT NULL,
 `county` varchar(15) NOT NULL,
 `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- RELATIONS FOR TABLE `county`:
--
--
-- Dumping data for table `county`
--
INSERT INTO `county` (`id`, `county`, `enabled`) VALUES
(1, 'Placer', 1),
(2, 'Nevada', 1),
(3, 'Washoe', 1),
(4, 'El Dorado', 1),
(5, 'Douglas', 1),
(6, 'Carson', 1),
(7, 'Alpine', 1),
(8, 'N/A', 0);
--
-- Indexes for table `center`
--
ALTER TABLE `center`
 ADD PRIMARY KEY (`id`),
 ADD KEY `IDX_40F0EB2485E73F45` (`county_id`);
--
-- Indexes for table `county`
--
ALTER TABLE `county`
 ADD PRIMARY KEY (`id`);
ALTER TABLE `center`
 ADD CONSTRAINT `FK_40F0EB2485E73F45` FOREIGN KEY (`county_id`) REFERENCES `county` (`id`);
 
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 */;

• ypercubeᵀᴹ: The code you provided could not produce the error you show. There is no FOREIGN KEY constraint added.
• geoB: Good point! I missed that. Will fix. Plus, the mini-script now fails.
• ypercubeᵀᴹ: I run the script and I got no errors. What does this mean?: "If the following script is used in a db with no tables it will not error out if run 2 or more times. Only in the context of the full db will errors be thrown." That the error is not reproducible?
• ypercubeᵀᴹ: I suggest you either post a script that reproduces the error or try to find which statement exactly produces the error you get.
• geoB: The statement that produces the error appears above: Create table center .... I cannot provide the entire 10MB script as it includes client's sensitive data. I'm open to suggestions on how best to reduce the script to its minimum necessary to produce the error. There are 26 tables with many years of data.

Answer 1

Discovered the source of error. There is a third entity Contact that had a foreign key to both Center and County entities. By removing the county property and thus the foreign key from the Contact entity the error is removed. The trick now, though, will be to get this schema change into production.