Quand on cherche à créer une base de données, la bonne pratique est de passer par la création du Modèle Conceptuel des Données (MCD) ou directement le Modèle Logique des Données (MLD), de la méthode MERISE. Dans les deux cas, les relations entre les entités sont essentielles.

En pratique, lorsque l’on passe à la création sous MySQL, la plupart du temps, on perd ses relations qui assurent pourtant l’atomicité de la base de données.

Sous MySQL 5.0, il n’existe qu’un seul moteur de stockage permettant de gérer les clés étrangères : InnoDB.

InnoDB à été développé par Oracle. Un de ses avantages est que les fichiers peuvent s’auto-réparer après un crash, grâce à ses propres logs.
La taille maximum pour ce type de moteur est de 64To par tables.

Il est important, par contre de noter que les tables de type InnoDB sont performantes dans les environnements mixte : lecture/écriture…

Création des clés étrangères

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 CASCADE ON UPDATE CASCADE
) TYPE=INNODB;

FOREIGN KEY se crée et se supprime comme une clé primaire.

Pour assurer l’atomicité d’une base de données, il existe les options :

• ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}
• ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}

Dans notre exemple, on peut alors décider, par exemple de supprimer toute une famille, en supprimant simplement le parent.
De même, si l’on change l’identifiant du parent, les enfant seront modifiés en cascades :

mysql> INSERT INTO parent VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO child VALUES(1,1),(2,1),(3,1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
|    3 |         1 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> UPDATE parent SET id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         2 |
+------+-----------+
3 rows in set (0.01 sec)