Comment les colonnes AUTO_INCREMENT fonctionnent avec InnoDB
<<<
Contraintes de clés étrangères FOREIGN KEY InnoDB et la réplication MySQL
>>>

16.7 Créer des tables InnoDB
16 Tables InnoDB
 Manuel de Référence MySQL 4.1 : Version Française

Comment utiliser les transactions de InnoDB avec différentes API
Convertir des tables MyISAM vers InnoDB
Comment les colonnes AUTO_INCREMENT fonctionnent avec InnoDB
->Contraintes de clés étrangères FOREIGN KEY
InnoDB et la réplication MySQL
Multiple tablespaces - putting each table into its own .ibd file

16.7.4 Contraintes de clés étrangères FOREIGN KEY

Depuis la version 3.23.43b, InnoDB respecte les contraintes de clé étrangères. InnoDB est le premier moteur de tables MySQL qui vous permet de définir des contraintes de clés étrangères, pour protéger l'intégrité de vos données.La syntaxe des définitions de contraintes de clés étrangères de InnoDB est la suivante :

[CONSTRAINT [symbol]] FOREIGN KEY (index_col_name, ...)
                  REFERENCES table_name (index_col_name, ...)
                  [ON DELETE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]
                  [ON UPDATE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]
Les deux tables doivent être de type InnoDB, dans la table, il doit y avoir un INDEX où les clés étrangères sont listées comme premières colonnes, dans le même ordre, et dans la table référencée, il doit y avoir un INDEX où les colonnes référencées sont listées comme premières colonnes, dans le même ordre. InnoDB ne crée pas automatiquement les index nécessaires pour les clés étrangères : vous devez ls créer vous-même. Les index sont nécessaires pour accélérer les vérifications de contrainte, et éviter un scan de table.Les colonnes correspondantes de la contrainte dans la table et la table de référence doivent avoir le même type, pour éviter les conversions lors des comparaisons. La taille et la présente du signe pour les entiers doit être les mêmes . La taille des chaînes doivent être les mêmes. Si vous spécifiez une action SET NULL , assurez vous que vous n'avez pas déclaré les colonnes de la table fille NOT NULL .

Si MySQL vous retourne une numéro d'erreur 1005 lors de la comande CREATE TABLE , et un message d'erreur de numéro 150, alors la création de la table a échoué à cause de la contrainte de clé étrangère, qui n'a pas été correctement formulée. Similairement, si une commande ALTER TABLE échoue et indique une erreur 150, c'est que la définition de la clé étrangère est incorrectement formulée dans la table modifiée. Depuis la version 4.0.13, vous pouvez utiliser la commande SHOW INNODB STATUS pour avoir une explication détaillée de la dernière erreur de clé étrangère InnoDB sur le serveur.

Depuis la version 3.23.50, InnoDB ne vérifie pas la clé étrangère pour les clés étrangères ou les clés référencées qui contienent des valeurs NULL .

Une entorse aux standards : si dans la table parente, il y a plusieurs lignes qui ont la même valeur de clé référencée, alors InnoDB effectue les vérifications de clé étrangères comme si les autres parents avec la même valeur de clé n'existaient pas. Par exemple, si vous avez défini une contrainte de type RESTRICT et qu'il y a une ligne fille avec plusieurs lignes parente, InnoDB n'acceptera pas l'effacement d'aucun des parents.

Depuis la version 3.23.50, vous pouvez aussi associer la clause ON DELETE CASCADE ou ON DELETE SET NULL avec la contrainte de clé étrangère. Les optoins correspondante ON UPDATE sont disponibles depuis la version 4.0.8. Si ON DELETE CASCADE est spécifiée, et qu'une ligne de la table parente est effacée, alors InnoDB va automatiquement effacer toute les lignes qui sont dans la table fille et dont les valeurs de clé étrangère sont celles référencées dans la ligne parente. Si ON DELETE SET NULL est spécifiée, les lignes filles sont automatiquement modifiée pour que la colonne de la clé étrangère prenne la valeur de NULL .

Une entorse aux standards : si ON UPDATE CASCADE ou ON UPDATE SET NULL cascade récursivement jusqu'à la même table, elle agira comme pour un RESTRICT . Cela est fait pour éviter les boucles infinies des modifications en cascade. Une clause ON DELETE SET NULL auto-référente, d'un autre coté, fonctionne depuis la version 4.0.13. La clause ON DELETE CASCADE auto-référente à toujours fonctionné.

Un exemle :

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
             FOREIGN KEY (parent_id) REFERENCES parent(id)
             ON DELETE SET NULL
) TYPE=INNODB;
Un exemle complexe :

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) TYPE=INNODB;
Depuis la version 3.23.50, InnoDB vous permet d'ajouter une nouvelle clé à une table, grâce à la syntaxe

ALTER TABLE yourtablename
ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES anothertablename(...)
[on_delete_and_on_update_actions]
Remember to create the required indexes first , though.Depuis la version 4.0.13, InnoDB supporte la syntaxe

ALTER TABLE yourtablename DROP FOREIGN KEY internally_generated_foreign_key_id
Vous devez utiliser SHOW CREATE TABLE pour déterminer l'identifiant interne de clé étrangère généré, lorsque vous voulez supprimer la clé étrangère. Dans InnoDB en versions < 3.23.50, ALTER TABLE et CREATE INDEX ne doivent pas être utilisé avec des tables qui ont des contraintes de clés étrangères, ou qui sont référencées dans des clés étrangères : une commande ALTER TABLE supprime toutes les clés étrangères qui sont définies pour cette table. Vous ne devriez pas utiliser ALTER TABLE sur la table référencée, mais utiliser DROP TABLE puis CREATE TABLE pour modifier le schéma. Lorsque MySQL exécute la commande ALTER TABLE , il risque d'utiliser en interne la commande RENAME TABLE , et cela va poser des problèmes pour les clés étrangères qui reposent sur cette table. Une commande CREATE INDEX est traitée par MySQL comme une commande ALTER TABLE , et ces restrictions s'appliquent aussi.Lorsqu'il vérifie les clés étrangères, InnoDB pose des verrous de lignes partagées sur les lignes des tables qu'il utilise. InnoDB vérifie immédiatement les contraintes de clés étrangères : la vérification n'attend pas la validation de la transaction.

Si vous voulez ignorer les contraintes de clés étrangères durant, par exemple, une opération de LOAD DATA , vous pouvez utiliser la commande SET FOREIGN_KEY_CHECKS=0 .

InnoDB vous permet d'effacer n'importe quelle table, même si cela va casser les contraintes de clés étranègres qui référence cette table. Lorsque vous supprimez une table, la contrainte de clé étrangère qui y était attachée est aussi supprimée.

Si vous recréez une table qui a été supprimée, sa définition doit se conformer aux contraintes des clés étrangères qui la référencent. Elle doit avoir les bons types et noms de colonnes, et doit avoir les bonnes clés, comme indiqué ci-dessus. Si ces contraintes ne sont pas vérifiées, MySQL vous gratifiera d'une erreur 1005, et vous enverra lire le message numéro 150.

Depuis la version 3.23.50 InnoDB retourne la définition de clé étrangère lorsque vous utilisez la commande

SHOW CREATE TABLE yourtablename
De plus, mysqldump produit aussi les définitions correctes de tables, sans oublier les clés étrangères.Vous pouvez aussi lister les clés étrangères d'une table T avec

SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'
Les contraintes de clés étragnères sont listées dans les commentaires de la table.

<< Contraintes de clés étrangères FOREIGN KEY >>
Comment les colonnes AUTO_INCREMENT fonctionnent avec InnoDB Créer des tables InnoDB InnoDB et la réplication MySQL