[SQL] clef étrangèreRésolu/Fermé

Question

Bonjour,
J'aimerais installer des clef étrangères à 3 de mes tables comme suit :


table_1 est la table mère.
la clef primaire est lié à id_table_1 de la table_2
et la clef primaire de table_2 est lié à id_table_2 de la table 3.

Ce que j'aimerais c'est que lorsque je modifie la clef primaire de la table_1, id_table_1 de table_2 se modifie automatiquement.
par exemple si ma clef primaire est égal à 2, si je la remplace par 1, tous les 2 de la colone id_table_1 dans table_2 se modifie et passe de 2 à 1.

C'est possible? c'est bien à ça que sert les clef étrangère?

Merci pour vos réponses.

Smoking bird · Answer

je crois que c'est à peu près ça, à cette petite différence près (qu'il faudra confirmer^^):
les clefs primaires en principe sont uniques :D donc tu as peu de chance d'avoir plusieurs champs de clefs primaires ayant la même valeur dans la même table^^. Sinon pour le reste, soit ça va tout seul, soit tu passes par un update inner join, mais je crois pas que ce soit nécessaire^^

okuni · Answer

Ok merci :)
Pour l'exemple, lors d'un update de clef primaire c'est dans le but de remplacer les ligne de donnée que j'ai supprimé.

J'ai trouvé cette ligne de code sur le net mais les explications n'étais pas très convaincante.
foreign key (nom1) références table2 on update cascade
Est-ce bien correcte?
Où dois-je mettre ce code?

Smoking bird · Answer

Ok, pour commencer, réfère toi à la doc officielle de mysql ;) elle est blindée d'infos mais demande un anglais correct^^ (même s'il y a des pages en français, la plupart d'entre elles étant moins complètes^^).
Tu verras dessus l'utilisation de on duplicate key update, qui doit être l'instruction que tu évoque. Elle permet, lors de l'insertion d'un nouveau champ, de faire des modifications si la clef primaire existe déjà^^ (c'est magique, crois moi:D)

L'erreur que tu obtiens me semble carrément bizarre, mais je pense avoir trouvé la même page que toi:
http://mysql.ifrance.com/showthread.php?t=252

J'ai trouvé des infos ici:
https://docs.microsoft.com/fr-fr/previous-versions/sql/sql-server-2008-r2/ms186973(v=sql.105)?redirectedfrom=MSDN
et là:
http://dev.mysql.com/doc/refman/5.0/fr/innodb-foreign-key-constraints.html

de quoi t'éclairer^^.

Si j'ai bien compris ce qu'ils disent sur le poste de mysql.ifrance, il faut que tu mettes un index sur ta clef étrangère, vérifie si c'est déjà le cas^^, l'erreur 150 ne devrait plus se produire.

Je t'avoue que j'ai un peu de mal à comprendre la structure de tes tables et l'utilisation que tu veux en faire^^.
Avec un modèle normalisé, théoriquement, tu n'update jamais la valeur de tes clefs primaires. Peux tu m'en dire plus, sur leur fonctionnement et sur ce que tu veux obtenir plus précisément?

okuni · Answer

Si j'ai bien compris ce qu'ils disent sur le poste de mysql.ifrance, il faut que tu mettes un index sur ta clef étrangère, vérifie si c'est déjà le cas^^, l'erreur 150 ne devrait plus se produire.
tous les champs que j'utilise pour les clef primaire sont indexé pourtant l'erreur persiste.

en fait, j'ai 3 tables différentes. la première contient les données sur les séries (TV) la 2ème sur les épisodes et la dernière sur les liens de redirection.

étant légèrement maniaque, je ne supporte pas trop de voir des "trous" dans la continuité des mes clef primaire (du à une suppression de ligne) donc j'aimerais utiliser les cléf étrangère pour modifier toutes les clef primaire rapidement.
et puis, puisque les séries et épisode sont liés et que épisode et lien sont liés, ce sera plus pratique d'utiliser les clef étrangères.

Merci pour la doc, je verrai ça ce soir :)

Smoking bird · Answer

Ok j'comprends mieux^^ moi aussi j'suis maniaque à mes heures^^.

Cela dit, ta démarche me semble assez inappropriée sur le coup: plus tu modifie tes données, plus elles sont fragilisées, et s'il y a quelque chose qui doit vraiment être solide dans tes tables, c'est bien tes clefs primaires^^.

J'ai opté pour une solution différente il y a déjà pas mal de temps, qui m'apporte entière satisfaction: mes clefs primaires sont des références construites par une petite fonction perso, qui renvoi une suite parfaitement aléatoire et avec un bon potentiel d'unicité d'une dizaine de caractères alphanum. Du coup, mes clefs primaires ne sont jamais touchées, et ne sont donc jamais fragilisées. L'avantage: tu n'auras plus ce problème de 'trous', et tes clefs seront sauvées, l'inconvénient: tu n'auras plus cette continuité logique à laquelle tu tiens ;). A toi de voir^^.

Cela dit, normalement ce que tu cherches est tout à fait possible (bien que je le déconseille fortement^^). Personnellement, je le ferais en plusieurs requêtes, dépendant des différentes actions que tu feras. Ca me semble impossible à faire en une requête^^.

On va faire le point:
tu veux pouvoir supprimer des séries + supprimer les épisodes associés + supprimer les liens associés aux épisodes

supprimer des épisodes et supprimer les liens associés

C'est bien ça?


(j'suis un peu mou aujourd'hui, donc excuse moi si ça te paraît laborieux mdr^^)

okuni · Answer

Oui c'est bien ce que je recherche, en ajoutant la modification. :)

En quoi je peux fragiliser mes tables en les modifiants?

Je ne comprend pas trop le principe de ton système.

Smoking bird · Answer

tu parles de mon système de référence?

exemple:
table serie
ref nom
table episode
ref_serie ref_episode nom
table lien
ref_episode lien

insert into serie
values ('012547hgt6','doctor who'),('1f2rgt546h','medium');

insert into episode
values ('012547hgt6','5edfr89745','Le retour des Daleks'),('012547hgt6','5frt4c4d5g','Planète morte'),
('1f2rgt546h','d4f5rfg47t','Le médecin fou'),('1f2rgt546h','gfht5h4jku','Démission');

j'ai pas fait le truc pour les liens, mais ça suit cette logique^^
Une référence unique et non modifiable pour chaque série, épisode et lien, qui correspond aux clefs primaires et fait donc le lien entre chaque contenu^^

j'ai rajouté deux séries, doctor who et medium, et deux épisodes pour chaque série, liés à la série adéquate grâce à la référence qui convient^^

Tes tables mysql sont concrètement parlant des fichiers stockés sur un serveur-> DD. Plus tu modifie un fichier, plus tu en fragilise les données. Je ne suis pas un expert, donc je pourrais pas te faire le détail pro^^ mais grosso modo tes données peuvent être déplacées sur le DD du serveur, ce qui les expose à être corrompues ou perdues (la perte est assez rare, mais plutôt grave^^, la corruption en revanche dépendra du nombre de modifications). Avoir des données corrompues sur des champs comme le titre d'une série ou d'un épisode, c'est pas dramatique, ça peut arriver et c'est facilement corrigeable. En revanche, avoir un problème avec une clef primaire ça va être bien plus casse-tête, c'est pour ça que les clefs sont uniques notamment, et qu'on utilise souvent un auto-increment: pour ne pas avoir à les modifier, et donc mettre le plus de chances de son côté pour qu'elles ne soient ni corrompues ni perdues.

Bien entendu, il y a l'entretient des tables, avec les analyses et optimisations, des procédures qui deviennent rapidement lourdes si mal utilisées, mais qui assurent l'intégrité de tes fichiers. A ce sujet, utilise tu la clause explain pour tes requêtes select? Tu remarqueras une nette différence dans tes performances en fonction de l'optimisation/analyse de tes tables, donc de l'entretient du fichier. En gros: un fichier bien entretenu t'apportera de meilleurs résultats et sera bien plus solide, admets que c'est intéressant ;).

la clause on delete cascade, si j'ai bien compris, devrait se poser soit en utilisant create table, soit en utilisant alter table, et concerne donc les clefs étrangères sur les bases innodb. Donc, toujours si je comprends bien la doc, lorsque cette clause est passée: la suppression d'une série possédant une clef étrangère liant aux épisodes supprimera automatiquement les épisodes concernés:

delete from serie where id_serie='$id_serie';

Avec les clefs étrangères adéquates reliant les liens aux épisodes, les liens seront aussi supprimés, il faut donc une clef sur la table épisodes.

Pour combler les trous dans la table série, après un delete:
update serie set id_serie-1 where id_serie>'$id_serie';

La clause on update cascade fera les ajustements par rapport à id_serie sur la table episodes.

Quel type de modification veux tu faire sur tes tables?

okuni · Answer

Merci pour ces éclaircissement :)

Je fais une optimisation des toutes mes tables chaque jour, je suppose que cela suffit pour éviter les corruptions?

pour les types de modifications, c'est simple, je dois être capable de modifier toutes les clauses. (y compris la clef primaire bien que tu commences à me faire peur à parler de corruption de donnée :p)

J'ai réessayer d'alterer mes tables pour les liers entre elles mais rien à faire, j'ai tous le temps la même erreur.
#1005 - Can't create table 'blizer.#sql-35b6_1204e' (errno: 150) 
Par contre quand j'essaye de lier la table episode avec lien, j'ai une autre erreur :
#1452 - Cannot add or update a child row: a foreign key constraint fails (`blizer`.`#sql-35b6_12063`, CONSTRAINT `#sql-35b6_12063_ibfk_1` FOREIGN KEY (`id`) REFERENCES `etr_lien` (`id_episode`) ON UPDATE CASCADE) 
Je comprend encore moins cette erreur :/

Encore merci pour ton aide.

Smoking bird · Answer

à ton service^^ :p

la fréquence d'optimisation dépend de la fréquence d'accès/ajout/modification/suppression de tes tables^^.
Si tu supprimes beaucoup, il te faudra optimiser souvent, si tu modifies beaucoup, il te faudra analyser souvent (si mes souvenirs sont bons, c'est comme ça que ça fonctionne^^).
Imaginons que tu fasses sur une table de 500 enregistrements (petit^^) 20 suppressions par jours, optimiser ta table une fois par semaine me semble suffisant. Si tu n'en fais que 2 ou 3, c'est pas utile d'optimiser tous les jours^^ ni même une fois par semaine^^. En gros, optimise en fonction de tes besoins uniquement^^, faut le faire au moment où c'est utile, mais pas au moment où ça devient gênant (quand il y a trop à faire par exemple^^).

Pour les modifications, tu parles de clauses ou de champs?

Pour les erreurs, tes tables sont elles vides?
Et c'est quoi ça: 'blizer.#sql-35b6_1204e' , ça te dit quelque chose? un nom de table ou quoi que ce soit? Si ça te dit rien, c'est peut être un nom de table temporaire que le serveur créé pour faire les modifs, sans y parvenir.

okuni · Answer

Merci pour l'info :)

pour les modif, je parles des champs, je ne sais pas pourquoi j'ai dis clauses.

Pour les erreurs, mes tables sont bien remplies, en fait, je travail sur des copies de mes tables actuels.
pour ceci : blizer.#sql-35b6_1204e
Je ne sais pas ce que c'est, juste blizer est le nom de ma base de donnée. donc #sql-35b6_1204e est surement une table temporaire mais je n'y ai pas accès.

Smoking bird · Answer

bon okay, pour les modifs, la modification des champs non répétés ne posera pas de soucis, procédure classique et ça ira. La modification des clefs étrangères devrait aussi passer grâce à on update cascade, si j'ai bien compris la doc^^ mais comme je t'ai dit, je ne recommande pas cette modification ;).

Pour les erreurs, merci pour les infos^^. En effet, ça doit être une table temporaire créée à cause de la présence de contenu: mysql doit être perdu dans la construction des clefs étrangères et du maintient de la cohérence des données. Je pense qu'il te faudra travailler sur des tables vides pour faire la structure qui t'intéresse, ce qui est toujours assez pénible quand il s'agit de remettre le contenu. J'ai eu ce genre de difficulté, ce qui m'a obligé à tirer un trait sur les clefs étrangères (mais le projet me le permettait^^, les jointures ont très bien fait l'affaire^^).

okuni · Answer

en vidant mes tables, j'ai toujours l'erreur #1005
par contre j'ai réussi à placer une clef étrangère entre lien et épisode mais si j'essaye d'ajouter un lien ayant une id_episode qui soit la même qu'une id (clef primaire) de la table episode, j'ai drois à l'erreur #1452

ça commence à me les chauffer lol

Smoking bird · Answer

courage, y'a forcément une solution ;)

tu peux me passer ton code mysql de création des tables avec clefs étrangères?

okuni · Answer

--serie
DROP TABLE IF EXISTS `etr_serie`;
CREATE TABLE `etr_serie` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serie` varchar(100) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serie` (`serie`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--episode
DROP TABLE IF EXISTS `etr_episode`;
CREATE TABLE `etr_episode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serie` tinyint(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unicite episode` (`titre`,`langue`),
  KEY `serie` (`serie`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
--lien
DROP TABLE IF EXISTS `etr_lien`;
CREATE TABLE `etr_lien` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_episode` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_episode` (`id_episode`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

-- Contraintes
ALTER TABLE `etr_episode` ADD FOREIGN KEY (`id`) REFERENCES `etr_lien` (`id_episode`) ON UPDATE CASCADE;
ALTER TABLE `etr_serie` ADD FOREIGN KEY (`id`) REFERENCES `etr_episode` (`serie`) ON UPDATE CASCADE;
voilà le code (j'ai allégé en supprimant des champs inutile

Smoking bird · Answer

Ca ça fonctionne:

DROP TABLE IF EXISTS `etr_serie`;
CREATE TABLE `etr_serie` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serie` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serie` (`serie`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


DROP TABLE IF EXISTS `etr_episode`;
CREATE TABLE `etr_episode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serie` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `serie` (`serie`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

DROP TABLE IF EXISTS `etr_lien`;
CREATE TABLE `etr_lien` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_episode` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_episode` (`id_episode`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;


ALTER TABLE `etr_serie` ADD FOREIGN KEY (`serie`) REFERENCES `etr_episode` (`serie`) ON UPDATE CASCADE;
ALTER TABLE `etr_episode` ADD FOREIGN KEY (`id`) REFERENCES `etr_lien` (`id_episode`) ON UPDATE CASCADE;

^^

et pour des raisons assez simples :p

quand tu fais correspondre des champs comme ça dans des tables, tu ôtes tout sens à ta démarche s'ils ne sont pas fondamentalement identiques^^. En clair, les champs serie dans series et episodes doivent posséder exactement le même type, sinon ils sont considérés comme étant différents, et mysql se perd ;). Fais bien gaffe à ça, c'est une erreur facile à reproduire. J'ai mis un varchar(50) pour les deux^^, c'est suffisant.

L'instruction alter table 'etr_serie' posait également problème, pour le même genre de raison: tu faisais correspondre serie.id à episode.serie, alors que dans la logique de ta conception, c'est plutôt serie.serie et episode.serie ;) et là ça fonctionne^^.

okuni · Answer

mais non car serie.serie c'est le nom de la série donc une chaine de caractère.
alors que serie.id c'est l'id de la serie donc un nombre que je vais correspondre à episode.serie
Tu as modifié la structure de la table episode, comme tu l'a fais, tout ma structure saute en l'air.
DROP TABLE IF EXISTS `etr_episode`;
CREATE TABLE `etr_episode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serie` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `serie` (`serie`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

en placant l'id de la série dans episode.serie, cela me permet de modifier le nom de la série dans la table série et d'automatiquement changer le nom pour les épisode puisque l'id est la même. C'est par jointure de table.

Smoking bird · Answer

Bon, c'est pas très clair tout ça^^
Je te recommande de mettre des noms de champs plus précis^^
Si serie correspond au nom de la serie, tu mets nom_serie, si ça correspond à l'id, id_serie s'il y a les deux, tu mets les deux, histoires que tout soit bien clair et bien compréhensible^^ comment veux tu que d'autres personnes s'y retrouvent sinon?^^;)

Ton dernier paragraphe est encore plus obscure pour moi, tu veux dire que tu stocke le nom de la série dans deux tables différentes?

okuni · Answer

voila un exemple concret de la structure de mes tables en entière :)
--
-- Structure de la table `raynal_video_serie`
--

DROP TABLE IF EXISTS `raynal_video_serie`;
CREATE TABLE `raynal_video_serie` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serie` varchar(100) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serie` (`serie`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Contenu de la table `raynal_video_serie`
--

INSERT INTO `raynal_video_serie` VALUES(1, 'Kyle XY');

--
-- Structure de la table `raynal_video_episode`
--

CREATE TABLE `raynal_video_episode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serie` tinyint(50) NOT NULL,
  `titre` varchar(100) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unicite episode` (`titre`),
  KEY `serie` (`serie`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Contenu de la table `raynal_video_episode`
--

INSERT INTO `raynal_video_episode` VALUES(1, 1, 'Kyle XY - S1 E1 Part1');

--
-- Structure de la table `raynal_video_lien`
--

DROP TABLE IF EXISTS `raynal_video_lien`;
CREATE TABLE `raynal_video_lien` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_episode` int(11) NOT NULL,
  `lien` varchar(250) COLLATE latin1_general_cs NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

--
-- Contenu de la table `raynal_video_lien`
--

INSERT INTO `raynal_video_lien` VALUES(2, 10, 'http://www.megavideo.com/?v=HV6ZOQSN');

Smoking bird · Answer

Bon, à partir de ton code j'ai renommé plus explicitement tes champs, et j'ai pu faire passer la clef sur la table serie, en modifiant les types qui ne correspondaient pas. Ca fonctionne et ça respecte la logique de ton travail^^. En revanche, j'ai pas encore trouvé pour la seconde clef^^

okuni · Answer

Encore merci pour ton aide :)
qu'a tu fais comme modification ?

Smoking bird · Answer

Comme je t'ai dit: j'ai veillé à la cohérence et à la clareté de ta structure, grâce aux exemples que tu m'as donné. Faut vraiment veiller à ce qu'un champ porte un nom qui représente exactement sa signification: un champ réservé à l'id d'une série doit s'appeller id_serie ou idSerie ou serie_id mais pas moins clair :D. Juste 'serie' c'est pas possible.
Du coup, n'importe qui comprend aisément les rapports logiques faits entre plusieurs tables (ex: 'Tiens, je comprends que l'id d'une série apparait sur cette table à cette emplacement et sur cette autre table à cet autre emplacement'). C'est idéal pour bien travailler, les choses doivent être le plus simple et le plus clair possible. Ceci fait, il faut vérifier que des champs aux noms et aux significations identiques possèdent également des types identiques, sinon ça foire. Un champ id_serie type varchar(10) sur une table ne correspond pas à un champ id_serie type int ou varchar(25) sur une autre table, par exemple, et donc les utiliser pour monter une clef étrangère est impossible.

L'étape suivante, c'est de t'assurer que des index sont posés sur les champs qui composent ta clef étrangère. Théoriquement, sur la table parent la clef primaire n'a pas besoin de se voir ajouter un index, puisque c'en est déjà un (si, bien entendu, la clef primaire fera partie de la clef étrangère^^). Donc tu vas sur la table enfant de la relation, tu cherches le champ qui complète la clef étrangère et tu le mets en index.

Et normalement, c'est à ce moment que tu peux placer ta clef étrangère, si tout se passe bien^^.

okuni · Answer

Ok merci beaucoup pour ton aide :)

[SQL] clef étrangère

22 réponses

Discussions similaires

Newsletters