Foreign key constraint is incorrectly formed, but why?

Question

I have an error about foreign key contraint but I don't know what's the reason...

When I type

CREATE TABLE `int_gtpro`.utilisateur_formation
(
id VARCHAR(36),
nom_user VARCHAR(35),
id_formation VARCHAR(36),
PRIMARY KEY (`id`) ,
FOREIGN KEY (`nom_user`) REFERENCES `int_gtpro`.`utilisateur`(`nom_user`),
FOREIGN KEY (`id_formation`) REFERENCES `int_gtpro`.`formation`(`id`)
);

I have

Error Code: 1005 Can't create table int_gtpro.utilisateur_formation (errno: 150 "Foreign key constraint is incorrectly formed")

When I type : show create table utilisateur

CREATE TABLE `utilisateur` (    
           `nom_user` varchar(35) NOT NULL,                                                              
           `date_creation` datetime DEFAULT NULL,                                                        
           `date_modification` datetime DEFAULT NULL,                                                    
           `user_modification` varchar(35) DEFAULT NULL,                                                 
           `nom` varchar(50) NOT NULL,   
           `prenom` varchar(50) DEFAULT NULL,                                                            
           `email` varchar(50) DEFAULT NULL,                                                             
           `actif` int(11) DEFAULT NULL, 
           `nb_jour_modif` int(11) DEFAULT 0,                                                            
           `text_police` varchar(50) DEFAULT 'System',                                                   
           `text_size` int(11) DEFAULT 0,
           `passwd` varchar(32) NOT NULL,
           `date_passwd` datetime NOT NULL,                                                              
           `mode_debug` int(11) DEFAULT 0,                                                               
           `telephone_fixe` varchar(20) DEFAULT NULL,                                                    
           `telephone_mobile` varchar(20) DEFAULT NULL,                                                  
           `nb_essai_mtc` int(11) DEFAULT NULL,                                                          
           `telephone_ip` varchar(20) DEFAULT NULL,                                                      
           `photo` varchar(150) DEFAULT NULL,                                                            
           `code_type_affichable` varchar(6) DEFAULT NULL,                                               
           `code_partenaire` varchar(3) DEFAULT NULL,                                                    
           `indicatif_tel` varchar(5) DEFAULT NULL,                                                      
           `debug_autorise` tinyint(1) DEFAULT 0,                                                        
           PRIMARY KEY (`nom_user`),     
           UNIQUE KEY `U_utilisateur_nom_user` (`nom_user`),                                             
           KEY `FK_utilisateur_code_type_affichable_type_affichable_code` (`code_type_affichable`),      
           CONSTRAINT `FK_utilisateur_code_type_affichable_type_affichable_code` FOREIGN KEY (`code_type_affichable`) REFERENCES `type_affichable` (`code`)              
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8

When I type : show create table formation

I have this:

CREATE TABLE `formation` (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
             `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
             `libelle` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
             `date_debut` datetime DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
             `date_fin` datetime DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
             `zone_geographique` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
             `auteur` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
             `date_creation` datetime DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
             `date_modification` datetime DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
             `actif` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
             PRIMARY KEY (`id`)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
           ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

So, what's the problem ?

Maybe the problem is in the table `formation`, so add that too please. Also check if your target table is MyISAM (e.g. do `show create table tablename` instead of `describe tablename`). — Solarflare, Sep 10 '20 at 08:15
One column is varchar(35), the other varchar(36). Also you don't need a unique key when it's already a primary key. — fancyPants, Sep 10 '20 at 09:03
@fancyPants varchar field lengths can be different. Collation and charset must match. — Shadow, Sep 10 '20 at 09:52

Foreign key constraint is incorrectly formed, but why?

0 Answers0