CODEWITHSUNDEEP
mysql
Thomas Bertoux
Thomas Bertoux

Reputation: 57

ERROR 1215 (HY000) at line: Cannot add foreign key constraint

I got "ERROR 1215 (HY000) at line 966: Cannot add foreign key constraint" error when I try to import database dump. Here is the line 966 :

CREATE TABLE `CATEGORIE_tags` (
      `categorie_id` int(11) NOT NULL,
      `tag_id` int(11) NOT NULL,
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `flag` tinyint(1) NOT NULL DEFAULT '0',
      `tags_id` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `tag_id` (`tag_id`),
      KEY `categorie_id` (`categorie_id`),
      KEY `FKACB212415C86F159` (`categorie_id`),
      KEY `FKACB21241925C59A` (`tag_id`),
      CONSTRAINT `CATEGORIE_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`),
      CONSTRAINT `CATEGORIE_tags_ibfk_2` FOREIGN KEY (`categorie_id`) REFERENCES `CATEGORIE` (`CAT_ID`),
      CONSTRAINT `FKACB212415C86F159` FOREIGN KEY (`categorie_id`) REFERENCES `CATEGORIE` (`CAT_ID`),
      CONSTRAINT `FKACB21241925C59A` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1331 DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;

Dou you know why ? Thanks

Upvotes: 0

Views: 2254

Answers (1)

Eperbab
Eperbab

Reputation: 378

If you import tables in a wrong order, constraint checks will fail.
Example: you create Category_tags table before tags table, and Category_tags references tags.

Fix: Disable foreign key checks while importing. 

SET FOREIGN_KEY_CHECKS = 0;

--  / import dump code here / 

SET FOREIGN_KEY_CHECKS = 1;

http://www.stetsenko.net/2008/10/mysql-how-to-ignore-checking-of-foreign-key-constraints-for-innodb-tables/

Upvotes: 1

Related Questions