-1

Sometimes when I shut down the xampp local server some table is "in use" and I can't open it or delete it. So I pick the last backup and drop the base, which is not possible because of the tables in use.

At Stack Overflow some said to manually delete the MySQL data in my phpmyadmin folder, ibd and frm files. But then I tried to import my backup and it said "Foreign key is incorrectly formed".

When I go to details it shows this:

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 */;

I can't find why it does this every time I reboot my computer so reboot xampp.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Guillaume OSTORERO
  • 59
  • 3
  • 12
  • 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
  • Does this answer your question? [Can a foreign key reference a non-unique index?](https://stackoverflow.com/questions/588741/can-a-foreign-key-reference-a-non-unique-index) – philipxy Aug 10 '22 at 23:26

1 Answers1

0

In MySQL a foreign key can't reference a non-unique field so I had to delete all the foreign keys that matched with this.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Guillaume OSTORERO
  • 59
  • 3
  • 12