Relationnel en InnoDB : quels indexes sont nécessaires ?

**guidav** · 20/09/2006, 11h01

Bonjour

Lors de la liaison de 2 tables en innoDB, est-il nécessaire que les 2 champs liés soient des indexes (où des clés primaires) à un champ, où est-il possible que l'un des champs fasse seulement partie d'un index à plusieurs champs ?

Merci de votre réponse.

**Maximil ian** · 20/09/2006, 11h09

Bonjour,

In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Starting with MySQL 4.1.2, such an index is created on the referencing table automatically if it does not exist.

http://dev.mysql.com/doc/refman/4.1/...nstraints.html

Autrement dit, un index multiple peut faire l'affaire si la clé étrangère est la première colonne de cet index.

**guidav** · 20/09/2006, 11h36

OK merci, donc si j'ai bien compris, si je veux mettre plusieurs contraintes sur plusieurs champs, il faudra quand même utiliser plusieurs indexes. C'est donc ce que je vais faire de ce pas

**Maximil ian** · 20/09/2006, 11h53

Envoyé par guidav

OK merci, donc si j'ai bien compris, si je veux mettre plusieurs contraintes sur plusieurs champs, il faudra quand même utiliser plusieurs indexes.

Effectivement.

**guidav** · 20/09/2006, 17h49

J'ai eu beau essayer, je n'arrive pas à mettre de contrainte (j'essaye de le faire avec PHPMyAdmin). Il me dit qu'il n'y a pas d'index sur nom_droit, pourtant elle est bien présente.
J'aimerais que que le champ nom_droit de __auth_sites_profils_droits dépende de nom_droit de __auth_droits.

Merci de votre aide.

Voici la structure des tables :

Code :

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

1
2
3
4
5
6
7
8
-- Structure de la table `__auth_droits`
-- 
 
CREATE TABLE `__auth_droits` (
  `nom_droit` varchar(15) NOT NULL,
  `constante_droit` varchar(30) NOT NULL,
  PRIMARY KEY  (`nom_droit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

et

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
-- Structure de la table `__auth_sites_profils_droits`
-- 
 
CREATE TABLE `__auth_sites_profils_droits` (
  `site` varchar(25) NOT NULL,
  `profil` varchar(25) NOT NULL,
  `nom_droit` varchar(25) NOT NULL,
  UNIQUE KEY `site` (`site`,`profil`,`nom_droit`),
  KEY `droit` (`nom_droit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- Contraintes pour la table `__auth_sites_profils_droits`
-- 
ALTER TABLE `__auth_sites_profils_droits`
  ADD CONSTRAINT `__auth_sites_profils_droits_ibfk_1` FOREIGN KEY (`site`) REFERENCES `__auth_sites` (`site`) ON DELETE CASCADE ON UPDATE CASCADE;

**Maximil ian** · 20/09/2006, 20h15

Envoyé par guidav

Code :

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

 ADD CONSTRAINT `__auth_sites_profils_droits_ibfk_1` FOREIGN KEY (`site`) REFERENCES `__auth_sites` (`site`) ON DELETE CASCADE ON UPDATE CASCADE;

On peut avoir la définition de __auth_sites ?

**guidav** · 21/09/2006, 09h13

Oui, bien sûr :

Code :

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

1
2
3
4
5
6
7
8
-- Structure de la table `__auth_sites`
-- 
 
CREATE TABLE `__auth_sites` (
  `site` varchar(25) NOT NULL,
  `URL_racine` varchar(25) NOT NULL,
  PRIMARY KEY  (`site`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Et merci encore de t'intéresser à mon problème.

**Maximil ian** · 21/09/2006, 10h16

J'ai testé de mon côté, ça marche très bien. Essaie peut-être en-dehors de PHPMyAdmin.

Quelle est ta version de MySQL ?

**guidav** · 21/09/2006, 10h20

J'utilise MySQL 5.0.
Je vais faire le test en effectuant directement la requête, mais j'ai du mal à concevoir comment ça pourrait ne pas fonctionner avec PMA.

EDIT : je viens de faire le test avec la requête suivante

Code :

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

ALTER TABLE `__auth_sites_profils_droits` ADD CONSTRAINT `__auth_sites_profils_droits_ibfk_2` FOREIGN KEY ( `nom_droit` ) REFERENCES `__auth_sites` ( `nom_droit` ) ON DELETE CASCADE ON UPDATE CASCADE

et ça ne marche pas, j'ai comme message d'erreur (authentification étant le nom de la base)

Code :

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

#1005 - Can't create table '.\authentification\#sql-b00_15889.frm' (errno: 150)

**Maximil ian** · 21/09/2006, 10h32

Ca serait pas à cause de la différence de type des 2 colonnes (VARCHAR(15) et VARCHAR(25) ?)

Fais un "perror 150" en ligne de commande pour voir à quoi ça correspond sur ton système.

Par ailleurs moi je parlais de la clé étrangère entre __auth_sites_profils_droits.site et __auth_sites.site (celle avec l'index triple). Elle donne quel message d'erreur ?

**guidav** · 21/09/2006, 10h45

Par ailleurs moi je parlais de la clé étrangère entre __auth_sites_profils_droits.site et __auth_sites.site (celle avec l'index triple). Elle donne quel message d'erreur ?

C'est justement là où c'est étonnant : la relation fonctionne comme il faut, cf. la ligne (site est l'index triple)

Code :

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

1
2
ALTER TABLE `__auth_sites_profils_droits`
  ADD CONSTRAINT `__auth_sites_profils_droits_ibfk_1` FOREIGN KEY (`site`) REFERENCES `__auth_sites` (`site`) ON DELETE CASCADE ON UPDATE CASCADE;

Par contre, mysql refuse de faire une relation entre `__auth_sites_profils_droits` et __auth_droits, alors que la structure des tables __auth_droits est __auth_sites est similaire.
Est-ce que ca pourrait venir de l'index triple ?

**Maximil ian** · 21/09/2006, 11h07

OK j'ai compris :

Code :

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

1
2
ALTER TABLE `__auth_sites_profils_droits` 
ADD CONSTRAINT `__auth_sites_profils_droits_ibfk_2` FOREIGN KEY ( `nom_droit` ) REFERENCES `__auth_sites` ( `nom_droit` ) ON DELETE CASCADE ON UPDATE CASCADE

C'est REFERENCES `__auth_droits` ( `nom_droit` ) et pas REFERENCES `__auth_sites` ( `nom_droit` )...

**guidav** · 21/09/2006, 11h35

Je viens de rectifier en

Code :

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

1
2
3
ALTER TABLE `__auth_sites_profils_droits` ADD CONSTRAINT 
`__auth_sites_profils_droits_ibfk_2` FOREIGN KEY ( `droit` ) REFERENCES 
`__auth_droits` ( `nom_droit` ) ON DELETE CASCADE ON UPDATE CASCADE

mais ca n'arrange malheureusement rien : j'ai toujours le même message d'erreur (au passage, j'ai changé le nom de la clef et rectifié la taille du champs).

**Maximil ian** · 21/09/2006, 14h32

Etrange

Que donne la partie "foreign key errors" d'un SHOW INNODB STATUS ?

**guidav** · 21/09/2006, 14h57

Il donne :
LATEST FOREIGN KEY ERROR
------------------------
060921 11:32:57 Error in foreign key constraint of table authentification/#sql-b00_15c93:
FOREIGN KEY ( `droit` ) REFERENCES `__auth_droits` ( `nom_droit` ) ON DELETE CASCADE ON UPDATE CASCADE:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/mysql/en/In...nstraints.html
for correct foreign key definition.
------------

Est-ce que ça pourrait être un problème d'interclassement ? C'est la seule différence de structure entre les 2 champs que j'essaye de lier (un utf8, un latin1) ?

**Maximil ian** · 21/09/2006, 15h01

Pour l'interclassement, c'est possible, il faut tester.

Sinon je vois pas ce qui va pas avec ces tables. Si quelqu'un a plus d'idées...
Au pire déclare un bug sur le site de MySQL, ils réagissent vite en général.

**guidav** · 21/09/2006, 15h08

C'est bon, j'ai trouvé : c'était bien l'interclassement.
A noter, donc : on ne peut pas relier 2 champs qui n'ont pas le même interclassement. Il s'agit bien de l'interclassement du champs, et non de la table.
Merci beaucoup pour ton aide, Maximilian.

Je pense que ce problème aurait sa place dans la FAQ, je peux rédiger la question/réponse si tu veux.