title: MySQL Error 1215: Cannot add foreign key constraint between Parent and Children
body: <p>I am utterly disappointed as I failed to pinpoint to the exact reason, why my table creation is failing when I am trying to create a Foreign Key relation between my two tables. The SQL queries that I am using to create the 2 tables are as under:</p>
  
  <pre><code>CREATE TABLE `OneMD_DEA_EMEA_STG_CUSTOMER` (
    `Customer_Pkey` int(11) NOT NULL AUTO_INCREMENT,
    `CustomerID` varchar(15) NOT NULL,
    `DataType` varchar(10) NOT NULL,
    `SourceSystemName` varchar(10) NOT NULL,
    `SourceCountry` varchar(2) NOT NULL,
    `SrcDataRfrshDt` date NOT NULL,
    `StartDt` date NOT NULL,
    `EndDt` date NOT NULL,
    `Category` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`Customer_Pkey`,`CustomerID`),
    UNIQUE KEY `CustomerID_UNIQUE` (`CustomerID`),
    KEY `idx_OneMD_DEA_EMEA_STG_CUSTOMER` (`DataType`,`SrcDataRfrshDt`,`SourceCountry`,`EndDt`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  
  
  CREATE TABLE `STG_RELATION` (
        `Relation_Pkey` int(11) NOT NULL AUTO_INCREMENT,
        `RelationType` varchar(15) NOT NULL,
        `SourceDataType` varchar(5) NOT NULL,
        `SourceID` varchar(15) NOT NULL,
        `TargetDataType` varchar(5) NOT NULL,
        `TargetID` varchar(15) NOT NULL,
        `RltnPrmryID` varchar(50) NOT NULL,
        `SourceSystemName` varchar(10) DEFAULT NULL,
        `SourceCountry` varchar(2) NOT NULL,
        `SrcDataRfrshDt` date NOT NULL,
        `StartDt` date NOT NULL,
        `EndDt` date NOT NULL,
        `HCPHCOSubType` varchar(10) DEFAULT NULL,
        `HCPHCOLinkType` varchar(10) DEFAULT NULL,
        `HCOHCOSubType` varchar(10) DEFAULT NULL,
        `HCOHCOLinkType` varchar(10) DEFAULT NULL,
        PRIMARY KEY (`Relation_Pkey`,`SourceID`,`TargetID`,`RltnPrmryID`),
        UNIQUE KEY `Relation_Pkey_UNIQUE` (`Relation_Pkey`),
        KEY `idx_STG_RELATION` (`RelationType`,`SrcDataRfrshDt`,`SourceCountry`,`EndDt`),
        KEY `Source_ID_idx` (`SourceID`),
        KEY `Target_ID_idx` (`TargetID`),
        CONSTRAINT `Source_ID` FOREIGN KEY (`SourceID`) REFERENCES `STG_CUSTOMER` (`CustomerID`) ON DELETE CASCADE ON UPDATE NO ACTION,
        CONSTRAINT `Target_ID` FOREIGN KEY (`TargetID`) REFERENCES `STG_CUSTOMER` (`CustomerID`) ON DELETE CASCADE ON UPDATE NO ACTION
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
      CREATE TABLE `STG_RELATION_ROLE` (
            `RltnRole_PKey` int(11) NOT NULL AUTO_INCREMENT,
            `SourceSystemName` varchar(10) NOT NULL,
            `ActivityID` varchar(15) NOT NULL,
            `RltnFrgnID` varchar(50) NOT NULL,
            `SrcDataRfrshDt` date NOT NULL,
            `StartDt` date NOT NULL,
            `EndDt` date NOT NULL,
            `SourceCountry` varchar(2) NOT NULL,
            `HCPHCORoleType` varchar(10) DEFAULT NULL,
            `HCPHCORoleField` varchar(10) DEFAULT NULL,
            `HCPHCORole` varchar(10) DEFAULT NULL,
            `HCPHCORoleStatus` varchar(20) DEFAULT NULL,
            PRIMARY KEY (`RltnRole_PKey`,`SourceSystemName`,`ActivityID`,`RltnFrgnID`),
            KEY `idx_STG_RELATION_ROLE` (`SrcDataRfrshDt`,`SourceSystemName`,`SourceCountry`,`EndDt`),
            KEY `Rltn_Frgn_ID_idx` (`RltnFrgnID`),
            CONSTRAINT `RltnFrgnID` FOREIGN KEY (`RltnFrgnID`) REFERENCES `STG_RELATION` (`RltnPrmryID`) ON DELETE CASCADE ON UPDATE NO ACTION
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>The Table RELATION got created successfully, but whenver I am trying to create the child table (RELATION_ROLE with parent as RELATION), the table creation is failing with the </p>
  
  <blockquote>
    <p>Error 1215: Cannot Add Foreign Key Constraint</p>
  </blockquote>
  
  <p>error message.</p>
  
  <p>Am I missing something here?</p>
  
  <p>Please note that CUSTOMER is the main table with child as RELATION (Customer_ID acting as Primary and Foreign Key) which further has a child RELATION_ROLE (RltnPrmryID is the Primary Key while RltnFrgnID is the foreign key.</p>
  
  <p>Please help me to get the issue resolved.</p>