Can't find why Foreign key is incorrectly formed

Question

Sometimes when i shutdown xampp local server some datatable is "in use" and i can't open them or delete them. So i pick last backup and drop the base, wich is not possible because of the tables in use.

So in stackoverflow some guys said to manually delete the mysql data in phpmyadmin folder ibd and frm files. And then i try to import my backup and they said "Foreign key is incorrectly formed.

When i go to details its show this text :

LATEST FOREIGN KEY ERROR

2019-05-15 17:09:40 1c6c Error in foreign key constraint of table service_tunnels/se_situer_a: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint: , CONSTRAINT "FK_Se_situer_a_IDEmplacement" FOREIGN KEY ("IDEmplacement") REFERENCES "emplacement" ("IDEmplacement")

Here is the sql part for table emplacement and table se_situer_a :

    DROP TABLE IF EXISTS `emplacement`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `emplacement` (
      `IDEmplacement` int(11) NOT NULL,
      `NomEmplacement` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
      `PointKilometrique` float DEFAULT NULL,
      `NumLocal` int(11) NOT NULL,
      `CodeOuvrage` varchar(25) COLLATE latin1_general_ci NOT NULL,
      PRIMARY KEY (`IDEmplacement`,`NumLocal`,`CodeOuvrage`),
      KEY `FK_Emplacement_CodeOuvrage` (`CodeOuvrage`),
      CONSTRAINT `FK_Emplacement_CodeOuvrage` FOREIGN KEY (`CodeOuvrage`) REFERENCES `ouvrage` (`CodeOuvrage`),
      CONSTRAINT `FK_Emplacement_NumLocal` FOREIGN KEY (`NumLocal`) REFERENCES `local` (`NumLocal`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
    /*!40101 SET character_set_client = @saved_cs_client */;


    DROP TABLE IF EXISTS `se_situer_a`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `se_situer_a` (
      `DateMiseEnPlace` date DEFAULT NULL,
      `DateEnlevement` date DEFAULT NULL,
      `IDEquipement` int(11) NOT NULL,
      `DateEmplacement` date NOT NULL,
      `IDEmplacement` int(11) NOT NULL,
      `NumLocal` int(11) NOT NULL,
      `CodeOuvrage` varchar(25) COLLATE latin1_general_ci NOT NULL,
      PRIMARY KEY (`IDEquipement`,`DateEmplacement`,`IDEmplacement`,`NumLocal`,`CodeOuvrage`),
      KEY `FK_Se_situer_a_DateEmplacement` (`DateEmplacement`),
      KEY `FK_Se_situer_a_IDEquipement` (`IDEquipement`),
      KEY `FK_Se_situer_a_CodeOuvrage` (`CodeOuvrage`),
      CONSTRAINT `FK_Se_situer_a_CodeOuvrage` FOREIGN KEY (`CodeOuvrage`) REFERENCES `ouvrage` (`CodeOuvrage`),
      CONSTRAINT `FK_Se_situer_a_DateEmplacement` FOREIGN KEY (`DateEmplacement`) REFERENCES `dateemplacement` (`DateEmplacement`),
      CONSTRAINT `FK_Se_situer_a_IDEmplacement` FOREIGN KEY (`IDEmplacement`) REFERENCES `emplacement` (`IDEmplacement`),
      CONSTRAINT `FK_Se_situer_a_IDEquipement` FOREIGN KEY (`IDEquipement`) REFERENCES `equipement` (`IDEquipement`),
      CONSTRAINT `FK_Se_situer_a_NumLocal` FOREIGN KEY (`NumLocal`) REFERENCES `local` (`NumLocal`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
    /*!40101 SET character_set_client = @saved_cs_client */;

Thanks you very much in advance, ican't find why it does this every time i reboot my computer so reboot xampp...

I think is because your foreign key point to a non unique field https://stackoverflow.com/questions/588741/can-a-foreign-key-reference-a-non-unique-index — Juan Carlos Oropeza, May 15 '19 at 15:31
Thaks for your response. So i need to delete the foreign key in the table where the IDEmplacement is repeated in several rows ? because foreign key can't reference to a non unique field. — Guillaume OSTORERO, May 15 '19 at 15:53
Is it normal that i have 3 KEY rows and 5 CONSTRAINT rows in the table "se_situer_a" ? — Guillaume OSTORERO, May 15 '19 at 15:56
Do _not_ manipulate files in mysql's tree. WIth InnoDB, it is not possible (with few exceptions). — Rick James, May 26 '19 at 05:20

score 0 · Answer 1 · answered May 16 '19 at 06:18

0

Thanks to Juan Carlos Oropeza i found out why is it happens. In MySQL a foreign key can't reference to a non unique field so i had to delete all the foreign key that match with this.

Thanks you, and thanks to stackoverflow !

answered May 16 '19 at 06:18

Guillaume OSTORERO

61
1
12

Are you sure that a referenced column must be `UNIQUE`? – Rick James May 26 '19 at 05:30

Can't find why Foreign key is incorrectly formed

1 Answers1