Bien, mais cela ne suffit pas ! Il faut également modifier notre table Animal. Nous allons ajouter une colonne espece_id , qui
contiendra l'id de l'espèce à laquelle appartient l'animal, et remplir cette colonne. Ensuite nous pourrons supprimer la colonne
espece, qui n'aura plus de raison d'être.
La colonne espece_id sera une clé étrangère ayant pour référence la colonne id de la table Espece. Je rappelle donc que ça
signifie qu'il ne sera pas possible d'insérer dans espece_id un nombre qui n'existe pas dans la colonne id de la table Espece.
La table Animal
Je vous conseille d'essayer d'écrire vous-même les requêtes avant de regarder comment je fais.
Secret (cliquez pour afficher)
Code : SQL
-- Ajout d'une colonne espece_id
ALTER TABLE Animal ADD COLUMN espece_id SMALLINT UNSIGNED; -- même
type que la colonne id de Espece
-- Remplissage de espece_id
UPDATE Animal SET espece_id = 1 WHERE espece = 'chien';
UPDATE Animal SET espece_id = 2 WHERE espece = 'chat';
UPDATE Animal SET espece_id = 3 WHERE espece = 'tortue';
UPDATE Animal SET espece_id = 4 WHERE espece = 'perroquet';
-- Suppression de la colonne espece
ALTER TABLE Animal DROP COLUMN espece;
-- Ajout de la clé étrangère
ALTER TABLE Animal
ADD CONSTRAINT fk_espece_id FOREIGN KEY (espece_id) REFERENCES
Espece(id);
Pour tester l'efficacité de la clé étrangère, essayons d'ajouter un animal dont l'espece_id est 5 (qui n'existe donc pas dans la table
Espece) :
Code : SQL
INSERT INTO Animal (nom, espece_id, date_naissance)
VALUES ('Caouette', 5, '2009-02-15 12:45:00');
Code : Console
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`el
Elle est pas belle la vie ?
J'en profite également pour ajouter une contrainte NOT NULL sur la colonne espece_id . Après tout, si espece ne pouvait pas
être NULL, pas de raison qu'espece_id puisse l'être ! Ajoutons également l'index UNIQUE sur (nom, espece_id ) dont on a déjà
discuté.
Secret (cliquez pour afficher)
Code : SQL
ALTER TABLE Animal MODIFY espece_id SMALLINT UNSIGNED NOT NULL;
CREATE UNIQUE INDEX ind_uni_nom_espece_id ON Animal (nom,
espece_id);
Partie 2 : Index, jointures et sous-requêtes
95/414
www.openclassrooms.com