"Foreign key constraint is incorrectly formed"

**tta201519** · 23/11/2020, 14h58

Bonjour
Je n'arrive pas a résoudre un pb de création de clé étrangère. J'ai le message suivant:

#1005 - Ne peut créer la table `SecCat`.`Aides` (Errcode: 150 "Foreign key constraint is incorrectly formed")

le pb est dans le lien entre Aides et Nature_panier Lorsque j'utilise un type INT pour Nature_Panier(id_code_panier) et Aides(Panier), la création de la clé étrangère se passe bien. Le message d'erreur apparait avec un type varchar dans les colonne liées.
Problème que je ne rencontre par pour le lien entre Beneficiaires et communes.
Merci de votre aide.

Voici le code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
-- phpMyAdmin SQL Dump
-- version 5.0.3
-- <a href="https://www.phpmyadmin.net/" target="_blank">https://www.phpmyadmin.net/</a>
--
-- Hôte : localhost
-- Généré le : lun. 23 nov. 2020 à 07:05
-- Version du serveur :  10.4.14-MariaDB
-- Version de PHP : 7.4.11
 
CREATE DATABASE IF NOT EXISTS `SecCat` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `SecCat`;
 
DROP TABLE IF EXISTS `Aides`;
CREATE TABLE `Aides` (
  `id_aide` int(11) NOT NULL,
  `Dt_aide` date NOT NULL,
  `Benef` int(11) NOT NULL,
  `Benef_Nom` varchar(15) NOT NULL,
  `Benef_Prenom` varchar(15) NOT NULL,
  `Panier` varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
 
DROP TABLE IF EXISTS `Beneficiaires`;
CREATE TABLE `Beneficiaires` (
  `id_benef` int(11) NOT NULL,
  `Nom` varchar(15) NOT NULL,
  `Prenom` varchar(15) NOT NULL,
  `Ass` varchar(20) DEFAULT NULL,
  `Dt_demande` varchar(8) DEFAULT NULL,
  `Adulte` varchar(1) DEFAULT NULL,
  `Enfant` varchar(1) DEFAULT NULL,
  `Code_insee_com` varchar(5) NOT NULL,
  `téléphone1` varchar(14) DEFAULT NULL,
  `téléphone2` varchar(14) DEFAULT NULL,
  `dt_naissance` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
DROP TABLE IF EXISTS `Communes`;
CREATE TABLE `Communes` (
  `id_commune_INSEE` varchar(5) NOT NULL,
  `Code_postal` varchar(5) NOT NULL,
  `nom_commune_complet` varchar(28) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
DROP TABLE IF EXISTS `Nature_paniers`;
CREATE TABLE `Nature_paniers` (
  `id_code_panier` varchar(2) NOT NULL,
  `Type_panier` varchar(11) NOT NULL,
  `Valeur_panier` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE `Aides`
  ADD PRIMARY KEY (`id_aide`),
  ADD KEY `fk_benef` (`Benef`),
  ADD KEY `fk_panier` (`Panier`);
 
 
ALTER TABLE `Beneficiaires`
  ADD PRIMARY KEY (`id_benef`),
  ADD KEY `Nom` (`Nom`,`Prenom`),
  ADD KEY `fk_commune` (`Code_insee_com`);
 
 
ALTER TABLE `Communes`
  ADD PRIMARY KEY (`id_commune_INSEE`);
 
 
ALTER TABLE `Nature_paniers`
  ADD PRIMARY KEY (`id_code_panier`);
 
ALTER TABLE `Aides`
  ADD CONSTRAINT `fk_benef` FOREIGN KEY (`Benef`) REFERENCES `Beneficiaires` (`id_benef`),
  ADD CONSTRAINT `fk_panier` FOREIGN KEY (`Panier`) REFERENCES `Nature_paniers` (`id_code_panier`)
;
 
ALTER TABLE `Beneficiaires`
  ADD CONSTRAINT `fk_commune` FOREIGN KEY (`Code_insee_com`) REFERENCES `Communes` (`id_commune_INSEE`);

**tatayo** · 23/11/2020, 16h01

Bonjour,
Tu donnes le même nom à un index et à une contrainte (fk_benef), je pense que c'est ça qui coince.

Tatayo.

**SQLpro** · 23/11/2020, 16h40

Votre message d'erreur n'a rien à voir avec le script donné. Apprenez à lire !

A +

**escartefigue** · 23/11/2020, 18h45

Quelques remarques au sujet de ce script :

je trouve plus simple de mettre les contraintes dans le CREATE TABLE plutôt que de les déporter dans un ALTER, ça semble un point de détail, mais ça permet d'avoir toutes les informations centralisées dans un seul ordre du DDL.
je milite contre les noms de FK différents de ceux de la PK quand ce n'est pas nécessaire. (Donc sauf cas de tables issues d'une association réflexive)

par exemple, pourquoi ADD CONSTRAINT `fk_panier` FOREIGN KEY (`Panier`) REFERENCES `Nature_paniers` (`id_code_panier`) ?.
Si la colonne s'appelle id_code_panier dans la table où elle est PK, autant conserver ce même nom dans toutes les tables où elle est FK, sans quoi il faut établir les correspondances de nom pour chaque nouvelle table où on retrouve cette colonne.
Quelle complication inutile !
Cette pratique est en générale symptomatique des modèles de données créés directement au niveau tabulaire, sans passer par le MCD. Car si on dérive le MLD ou le MPD depuis le MCD, c'est bien le même nom de colonne qui par défaut se propage dans toutes les tables
le type et la longueur des attributs n'est pas toujours bien choisi : du varchar(1) ou (2) et même (5) est une hérésie, du varchar pour un code postal n'est pas mieux, du varchar pour une date de naissance est une catastrophe , et du varchar pour une typologie est du même tonneau. Il existe d'autres types que le varchar, ce n'est pas pour rien !
enfin j'ai des doutes sur la modélisation : il semble que le code INSEE de la commune soit dans la table des bénéficiaires , la présence de deux téléphones dans cette table est également sujette à questions.

**tta201519** · 23/11/2020, 19h40

Envoyé par tatayo

Bonjour,
Tu donnes le même nom à un index et à une contrainte (fk_benef), je pense que c'est ça qui coince.

Tatayo.

Ce n'est pas cela. J'ai mis un prééfixe pour les nom d'index et rien n'y fait.
C'est bien la dernière requete qui pose problème.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
-- phpMyAdmin SQL Dump
-- version 5.0.3
-- https://www.phpmyadmin.net/
--
-- Hôte : localhost
-- Généré le : lun. 23 nov. 2020 à 07:05
-- Version du serveur :  10.4.14-MariaDB
-- Version de PHP : 7.4.11
 
CREATE DATABASE IF NOT EXISTS `SecCat` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `SecCat`;
 
DROP TABLE IF EXISTS `Aides`;
CREATE TABLE `Aides` (
  `id_aide` int(11) NOT NULL,
  `Dt_aide` date NOT NULL,
  `Benef` int(11) NOT NULL,
  `Panier` varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
 
DROP TABLE IF EXISTS `Beneficiaires`;
CREATE TABLE `Beneficiaires` (
  `id_benef` int(11) NOT NULL,
  `Nom` varchar(15) NOT NULL,
  `Prenom` varchar(15) NOT NULL,
  `Ass` varchar(20) DEFAULT NULL,
  `Dt_demande` varchar(8) DEFAULT NULL,
  `Adulte` varchar(1) DEFAULT NULL,
  `Enfant` varchar(1) DEFAULT NULL,
  `Code_insee_com` varchar(5) NOT NULL,
  `téléphone1` varchar(14) DEFAULT NULL,
  `téléphone2` varchar(14) DEFAULT NULL,
  `dt_naissance` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
DROP TABLE IF EXISTS `Communes`;
CREATE TABLE `Communes` (
  `id_commune_INSEE` varchar(5) NOT NULL,
  `Code_postal` varchar(5) NOT NULL,
  `nom_commune_complet` varchar(28) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
DROP TABLE IF EXISTS `Nature_paniers`;
CREATE TABLE `Nature_paniers` (
  `id_code_panier` varchar(2) NOT NULL,
  `Type_panier` varchar(11) NOT NULL,
  `Valeur_panier` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE `Aides`
  ADD PRIMARY KEY (`id_aide`),
  ADD INDEX `ind_benef` (`Benef`),
  ADD INDEX `ind_panier` (`Panier`);
 
ALTER TABLE `Beneficiaires`
  ADD PRIMARY KEY (`id_benef`),
  ADD INDEX `ind_nom` (`Nom`,`Prenom`),
  ADD INDEX `ind_commune` (`Code_insee_com`);
 
ALTER TABLE `Communes`
  ADD PRIMARY KEY (`id_commune_INSEE`);
 
 
ALTER TABLE `Nature_paniers`
  ADD PRIMARY KEY (`id_code_panier`);
 
ALTER TABLE `Beneficiaires`
  ADD CONSTRAINT `fk_commune` FOREIGN KEY (`Code_insee_com`) REFERENCES `Communes` (`id_commune_INSEE`);
 
ALTER TABLE `Aides`
  ADD CONSTRAINT `fk_benef` FOREIGN KEY (`Benef`) REFERENCES `Beneficiaires` (`id_benef`)*;
 
ALTER TABLE `Aides`
  ADD CONSTRAINT `fk_panier` FOREIGN KEY (`Panier`) REFERENCES `Nature_paniers` (`id_code_panier`);

**tta201519** · 23/11/2020, 19h49

Juste d'accord sur ton 3° point: certains types à revoir.
Mais je ne vois de pas de solution à ma questions.

Envoyé par escartefigue

Quelques remarques au sujet de ce script :

je trouve plus simple de mettre les contraintes dans le CREATE TABLE plutôt que de les déporter dans un ALTER, ça semble un point de détail, mais ça permet d'avoir toutes les informations centralisées dans un seul ordre du DDL.
je milite contre les noms de FK différents de ceux de la PK quand ce n'est pas nécessaire. (Donc sauf cas de tables issues d'une association réflexive)

par exemple, pourquoi ADD CONSTRAINT `fk_panier` FOREIGN KEY (`Panier`) REFERENCES `Nature_paniers` (`id_code_panier`) ?.
Si la colonne s'appelle id_code_panier dans la table où elle est PK, autant conserver ce même nom dans toutes les tables où elle est FK, sans quoi il faut établir les correspondances de nom pour chaque nouvelle table où on retrouve cette colonne.
Quelle complication inutile !
Cette pratique est en générale symptomatique des modèles de données créés directement au niveau tabulaire, sans passer par le MCD. Car si on dérive le MLD ou le MPD depuis le MCD, c'est bien le même nom de colonne qui par défaut se propage dans toutes les tables
le type et la longueur des attributs n'est pas toujours bien choisi : du varchar(1) ou (2) et même (5) est une hérésie, du varchar pour un code postal n'est pas mieux, du varchar pour une date de naissance est une catastrophe , et du varchar pour une typologie est du même tonneau. Il existe d'autres types que le varchar, ce n'est pas pour rien !
enfin j'ai des doutes sur la modélisation : il semble que le code INSEE de la commune soit dans la table des bénéficiaires , la présence de deux téléphones dans cette table est également sujette à questions.

**escartefigue** · 23/11/2020, 20h02

Modélisez votre base de données en utilisant un logiciel adapté, il en existe de nombreux sur le marché dont certains sont gratuits, ça vous évitera un grand nombre d'erreurs dont celle rencontrée ici

Parmi les gratuits, il existe Looping dont le concepteur est aussi l'un des contributeurs de developpez.net, vous pouvez télécharger Looping ici : https://www.looping-mcd.fr/

**Artemus24** · 24/11/2020, 15h49

Salut à tous.

Comme le dit si bien SQLPRO, apprenez à lire.
L'erreur en question est que vous utilisez deux "charset" différents.
L'un est en "utf8" et l'autre en "utf8mb4".

Il y a incompatibilité à cause de cette différence de "charset".

Corrigez vos "charset" en mettant partout la même chose et votre problème sera résolu !

@+

**tta201519** · 25/11/2020, 06h49

Merci pour vos remarques qui m'ont permis de résoudre le problème.
J'apprécie particulièrement le caractère convivial, aidant, joyeux ... et pour tout dire méprisant des commentaires .
Salutations à tous.

"Foreign key constraint is incorrectly formed"

Requêtes MySQL

Discussions similaires

Partager

Partager