Discussion :

[laurentSc]

Bonjour, voici un problème vu hier soir, mais je me suis dit que j'aurai les idées plus claires le matin, donc j'ai attendu ce matin et encore tenté de le résoudre. Comme ça veut pas, je me tourne vers le forum...
Dans MySql, j'ai 2 tables et veux déclarer une des colonnes de la première comme clé étrangère sur la seconde, mais...

DDL de la table ticket :

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
 
DROP TABLE IF EXISTS `ticket`;
CREATE TABLE `ticket` (
  `id_ticket` int(8) NOT NULL,
  `product_name` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `assigned_group` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `submitted_date` date NOT NULL,
  `last_resolved_date` date NOT NULL,
  `summary` varchar(200) COLLATE utf8_bin NOT NULL,
  `priority_key` smallint(2) NOT NULL,
  `status_key` smallint(2) NOT NULL,
  `country_key` smallint(2) NOT NULL,
  `submitter_SESA` int(6) NOT NULL,
  `customer_SESA` int(6) NOT NULL,
  `organization_key` smallint(2) DEFAULT NULL,
  `incident_key` tinyint(2) NOT NULL,
  `reported_source` varchar(20) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
ALTER TABLE `ticket`
  ADD PRIMARY KEY (`id_ticket`),
  ADD KEY `fk_Priority` (`priority_key`) USING BTREE,
  ADD KEY `fk_Status` (`status_key`) USING BTREE,
  ADD KEY `fk_organization` (`organization_key`);
 
--
-- AUTO_INCREMENT pour les tables déchargées
--
 
--
-- AUTO_INCREMENT pour la table `ticket`
--
ALTER TABLE `ticket`
  MODIFY `id_ticket` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7495669;
 
--
-- Contraintes pour les tables déchargées
--
 
--
-- Contraintes pour la table `ticket`
--
ALTER TABLE `ticket`
  ADD CONSTRAINT `fk_organization` FOREIGN KEY (`organization_key`) REFERENCES `organization` (`organization_key`) ON DELETE SET NULL ON UPDATE SET NULL;
COMMIT;

DDL de la table incident :

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
 
DROP TABLE IF EXISTS `incident`;
CREATE TABLE `incident` (
  `incident_key` tinyint(2) NOT NULL,
  `incident` varchar(13) COLLATE utf8_bin NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
--
-- Déchargement des données de la table `incident`
--
 
INSERT INTO `incident` (`incident_key`, `incident`) VALUES
(1, 'Incident');
 
--
-- Index pour les tables déchargées
--
 
--
-- Index pour la table `incident`
--
ALTER TABLE `incident`
  ADD PRIMARY KEY (`incident_key`);
 
--
-- AUTO_INCREMENT pour les tables déchargées
--
 
--
-- AUTO_INCREMENT pour la table `incident`
--
ALTER TABLE `incident`
  MODIFY `incident_key` tinyint(2) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
COMMIT;

requête SQL :

Code sql :

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

1
2
3
4
5
6
ALTER TABLE `ticket`
ADD CONSTRAINT `fk_incident` 
FOREIGN KEY (`incident_key`)
REFERENCES `incident` (`incident_key`)
ON DELETE SET NULL
ON UPDATE SET NULL;

et message d'erreur :

Ne peut créer la table `tickets`.`ticket` (Errcode: 150 "Foreign key constraint is incorrectly formed") (Détails…)

cependant, comme on peut le constater, ça a déjà marché. Donc, comment faire ici ?

[tatayo]

Bonjour,
Je vois un (détails...) dans le message d'erreur.
Peux-tu nous donner cette information ? Peut-être que la clé du problème s'y trouve.

A côté de cela, je vois que les colonnes impliquées sont "not null", et que dans la contrainte tu indiques un "set null".
N'est-ce pas contradictoire ? Pourquoi ne pas mettre un "restrict" dans la contrainte ?

Tatayo.

[laurentSc]

Merci de me répondre. Ta remarque sur le NOT NULL m'a paru judicieuse, donc j'ai essayé en modifiant la requête :

Code sql :

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

1
2
3
4
5
6
ALTER TABLE `ticket`
ADD CONSTRAINT `fk_incident` 
FOREIGN KEY (`incident_key`)
REFERENCES `incident` (`incident_key`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

Y a toujours un message d'erreur, mais plus de détail (quand y en avait un, il me semblait ne rien donner d'utile) :

#1452 - Cannot add or update a child row: a foreign key constraint fails (`tickets`.`#sql-373c_895`, CONSTRAINT `fk_incident` FOREIGN KEY (`incident_key`) REFERENCES `incident` (`incident_key`))

Et pourquoi parle-t-il de child row alors qu'on est dans la table parent ?
Dans le message d'erreur, y a tickets avec un S alors qui y en a pas dans le nom de la table : tickets est le nom de la bdd.

[escartefigue]

Bonjour,

Il faut que la table parente soit créée avant la table enfant. Le CREATE TABLE `incident` doit donc précéder le CREATE TABLE `ticket`, sinon la contrainte ne peut pas être créée.

Par ailleurs, je confirme que l'instruction "ON DELETE SET NULL" n'est pas applicable sur une colonne "NOT NULL"

Enfin, le choix d'un type "TINYINT" me semble très optimiste pour identifier des incidents : êtes vous certain de ne jamais avoir plus de 255 incidents dans votre table...

[laurentSc]

Merci pour la réponse.

Je croyais que la table parente était celle comportant la clé étrangère (ici ticket). C'est l'inverse ?

Si je suis votre raisonnement, j'ai deletté la bdd, placé le CREATE TABLE `ticket` en dernier, puis ai réimporté. Mais toujours le message d'erreur

Suite à mon manque de précision, j'ai renommé le nom des colonnes de la table incident :

Code sql :

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

1
2
3
4
5
DROP TABLE IF EXISTS `incident`;
CREATE TABLE `incident` (
  `type_incident_key` tinyint(2) NOT NULL,
  `type_incident` varchar(13) COLLATE utf8_bin NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

donc oui, je pense qu'il y aura moins de 255 types d'incidents

[tatayo]

Est-ce que tu as des lignes dans les tables ? Est-ce que l'erreur arrive lors de la création, ou lors de l'insertion de lignes dans la table ?

Si j'interprète bien le message d'erreur, il semble que MySQL n'arrive pas à mettre à jour une ligne de la table car elle ne valide pas la contrainte.

Tatayo.

[laurentSc]

Bien vu ton idée de regarder les lignes. Pour être tranquille avec ça, j'ai vidé la table ticket et ça a marché ! Dans la foulée, j'avais une autre clé étrangère à créer ; c'est fait aussi et pour finir, j'ai remis les enregistrements dans la table ticket, et le tour est joué.

[escartefigue]

Merci pour la réponse.

Je croyais que la table parente était celle comportant la clé étrangère (ici ticket). C'est l'inverse ?

Si je suis votre raisonnement, j'ai deletté la bdd, placé le CREATE TABLE `ticket` en dernier, puis ai réimporté. Mais toujours le message d'erreur

Suite à mon manque de précision, j'ai renommé le nom des colonnes de la table incident :

Code sql :

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

1
2
3
4
5
DROP TABLE IF EXISTS `incident`;
CREATE TABLE `incident` (
  `type_incident_key` tinyint(2) NOT NULL,
  `type_incident` varchar(13) COLLATE utf8_bin NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

donc oui, je pense qu'il y aura moins de 255 types d'incidents

Du coup, pour être cohérent, il faudrait également renommer la table en `type_incident`

[laurentSc]

Oui ; c'est fait.