Discussion :

[Jasmine80]

Voici la structure de ma DB
structure


L'ennui est que pour un même id de fungi2, j'ai plusieurs entrées dans fungi2_accessions. Je voudrais supprimer les entrées de ces deux tables pour lesquelles fungi2.organism contient le mot 'uncultured'.

Code :

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

1
2
3
4
SELECT fungi2_accessions.*, fungi2.*
FROM fungi2_accessions, fungi2
WHERE fungi2_accessions.id = fungi2.id
AND fungi2.organism like '%uncultured%'

2359 entrées

Code :

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

1
2
3
4
5
 
DELETE
FROM fungi2_accessions, fungi2
WHERE fungi2_accessions.id = fungi2.id
AND fungi2.organism like '%uncultured%'

Avec cette requête, je crains de ne récupérer qu'une seule des entrées de fungi2_accessions associées à fungi2 ... serait-ce le cas?

Une jointure serait-elle mieux ... je n'arrive pas à voir le résultat de cette requête, je ne sais donc pas si le nombre d'entrées récupéré est supérieur ou non à la requête précédente.

Code :

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

1
2
3
4
SELECT * 
FROM fungi2 f
LEFT JOIN fungi2_accessions fa ON f.id = fa.id 
WHERE f.organism LIKE '%uncultured%'

Merci pour vos conseils,

[CinePhil]

Si vos tables sont en InnoDB, la contrainte ON DELETE CASCADE fonctionne et comme fungi2_accessions.id est clé étrangère référençant fungi2.id, supprimer une ligne de fungi2 supprimera les lignes en correspondance dans fungi2_accessions.

Sinon il faut d'abord supprimer les lignes dans fungi2_accessions puis dans fungi2. Autrement dit faire en deux requêtes ce que le SGBD est capable de faire tout seul avec une seule requête et une base correctement implémentée.

[Jasmine80]

Merci pour votre aide.

Si vos tables sont en InnoDB, la contrainte ON DELETE CASCADE fonctionne et comme fungi2_accessions.id est clé étrangère référençant fungi2.id, supprimer une ligne de fungi2 supprimera les lignes en correspondance dans fungi2_accessions.

Oui ce sont 2 table InooDB, je pense même que c'est obligatoire pour créer une FK, non? Je vais regarder ON DELETE CASCADE.

Sinon il faut d'abord supprimer les lignes dans fungi2_accessions puis dans fungi2. Autrement dit faire en deux requêtes ce que le SGBD est capable de faire tout seul avec une seule requête et une base correctement implémentée.

Ma base est-elle incorrectement implémentée? N'hésitez pas à me dire comment l'améliorer. Merci.

[CinePhil]

Puisque vos tables sont en InnoDB il n'y a pas de problème. Activez le ON DELETE CASCADE sur la clé étrangère et supprimez les lignes voulues de fungi2. Les lignes correspondantes dans fungi2_associations auront disparu elles aussi.

[Jasmine80]

Merci pour ton aide.

J'ai un autre problème, je ne suis pas certaine que la FK a bien été déclarée. Quand j'ai utilisé DBDesigner4, la FK est bien apparue sur le schéma par contre, je ne la retrouve pas dans la liste des index de la table dans PhpMyAdmin. Comment puis-je faire? Dois-je retourner dans DBD4 ou alors la créé avec une commande?

Code :

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

1
2
3
4
 
ALTER TABLE fungi2_accession
ADD FOREIGN KEY [id] (id)
REFERENCES fungi2 (id)

Code :

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

1
2
3
4
DELETE
FROM fungi2
WHERE fungi2.organism LIKE '%uncultured%'
ON DELETE CASCADE fungi2_accession.id

Merci.

[CinePhil]

ON DELETE CASCADE s'applique sur la déclaration de clé étrangère, donc dans le CREATE ou ALTER TABLE.

Ensuite, tu DELETE dans la table mère (celle qui donne sa clé primaire à l'autre en tant que clé étrangère) et les lignes en correspondance par la clé étrangère dans la table fille seront supprimées automatiquement.

[Jasmine80]

Depuis la version 3.23.50, InnoDB vous permet d'ajouter une nouvelle clé à une table, grâce à la syntaxe

Code :

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

1
2
3
4
5
ALTER TABLE yourtablename
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

N'oubliez pas de commencer par créer les index nécessaires en premier!. Vous pouvez aussi ajouter des clés étrangères reflexives, en utilisant la commande ALTER TABLE.

Que signifie une clé réflexives?

Voici une définition que j'ai trouvée :
Wikipédia : Une relation réflexive R de l'ensemble X est une relation pour laquelle pour tout a de X, a est R-relié à lui-même.
Les clés étrangères sont donc R-liées à elles même, qu'est-ce que cela signifie?

Donc avant de créer la FK, je dois indexer cette colonne? Pourquoi, cela ne devrait il pas se faire automatiquement?

Dans 'ADD FOREIGN KEY [id] (id)', à quoi sert le id entre crochets?

Code :

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

1
2
3
4
ALTER TABLE fungi2_accession
ADD FOREIGN KEY [id] (id)
REFERENCES fungi2 (id)
ON DELETE CASCADE

Merci,

[CinePhil]

Que signifie une clé réflexives?

Voici une définition que j'ai trouvée :
Wikipédia : Une relation réflexive R de l'ensemble X est une relation pour laquelle pour tout a de X, a est R-relié à lui-même.
Les clés étrangères sont donc R-liées à elles même, qu'est-ce que cela signifie?

C'est quand la clé étrangère fait référence à la table où elle se situe.
Exemple : une table de personnes avec des liens de parent à enfant.
Personnes (P_Id, P_Nom, P_Prenom, P_DateNaissance, P_IdPere, P_IdMere)

Donc avant de créer la FK, je dois indexer cette colonne? Pourquoi, cela ne devrait il pas se faire automatiquement?

Et ben non il ne le fait pas tout seul !
Je pense que la raison est que tu peux vouloir créer des index multi-colonnes et que l'une de ces colonnes serve ensuite de clé étrangère pour une autre table. Je n'ai pas d'exemple en tête d'un cas comme ça.

Dans 'ADD FOREIGN KEY [id] (id)', à quoi sert le id entre crochets?

Entre parenthèses se trouve le nom de la colonne, entre crochets se trouve le nom optionnel de l'index. Si tu veux donner un nom à l'index, supprime les crochets qui ne sont dans la doc que pour indiquer que c'est un paramètre optionnel. Et quitte à lui donner un nom, autant qu'il soit explicite :

Code :

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

1
2
3
4
ALTER TABLE fungi2_accession
ADD FOREIGN KEY id_fungi2 (id)
REFERENCES fungi2 (id)
ON DELETE CASCADE

[Jasmine80]

Code :

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

1
2
3
4
5
6
7
ALTER TABLE `fungi2_accessions` 
ADD INDEX ( `id` )  
 
ALTER TABLE fungi2_accessions 
ADD FOREIGN KEY id_fungi2( id ) 
REFERENCES fungi2( id ) 
ON DELETE CASCADE

fungi2_accessions.id apparait maintenant comme un index dans le tableau de PhpMyAdmin.

Cela fonctionne merci beaucoup pour ton aide, j'ai testé des requêtes DELETE sur une copie de mes tables.