Une requête ALTER TABLE permettra de définir une contrainte.
ALTER TABLE nom_table_avec_cle_etrangere
ADD CONSTRAINT nom_contrainte
FOREIGN KEY (cle_etrangere)
REFERENCES table_referencee(cle_primaire);
Par exemple, pour appliquer une contrainte d'intégrité référentielle à la table voitures :
On entrera la requête suivante :
ALTER TABLE voitures
ADD CONSTRAINT fk_voitures_clients
FOREIGN KEY (client_id)
REFERENCES clients (id);
Il n'est pas possible d'ajouter une contrainte d'intégrité référentielle entre n'importe quels champs. Pour que la contrainte fonctionne, les champs liés doivent respecter les conditions suivantes :
Par défaut, lorsqu'on crée une contrainte d'intégrité référentielle, InnoDB empêche la suppression d'un enregistrement de la table parent si un enregistrement de la table enfant fait référence à sa clé. Par exemple, il est impossible de supprimer le client dont le ID est 3 si une voiture est associée au client 3.
InnoDB empêche également la modification de la valeur d'une clé primaire lorsqu'elle est référencée par une clé étrangère.
Ce comportement est le même que si on avait précisé à la fin de la requête ALTER TABLE les clauses ON DELETE NO ACTION et ON UPDATE NO ACTION.
Ce comportement peut être modifié pour que InnoDB se charge de détruire automatiquement tous les enregistrements correspondant dans la table enfant (ON DELETE CASCADE) ou pour qu'il remplace la valeur de leur clé étrangère par NULL (ON DELETE SET NULL).
ALTER TABLE nom_table_avec_cle_etrangere
ADD CONSTRAINT nom_contrainte
FOREIGN KEY (cle_etrangere)
REFERENCES table_referencee(cle_primaire)
[ON DELETE {NO ACTION | CASCADE | SET NULL}]
[ON UPDATE {NO ACTION | CASCADE | SET NULL}];
Attention : l'utilisation de ON DELETE CASCADE ou de ON DELETE SET NULL peut avoir des conséquences graves car elles peuvent entraîner la modification ou la suppression d'un nombre important d'enregistrements dans la table enfant. Une erreur lors de la suppression d'un enregistrement de la table parent peut donc être très difficile à corriger...
À moins d'avoir une bonne raison, on préférera utiliser la clause ON DELETE NO ACTION.
Pour supprimer une contrainte d'intégrité référentielle, vous devez d'abord connaître le nom de la contrainte à supprimer.
La requête suivante permet de lister toutes les contraintes d'intégrité référentielle d'une base de données.
SELECT * FROM information_schema.referential_constraints
WHERE constraint_schema = 'nom_base_de_donnees';
Le nom des contraintes apparaîtra dans la colonne CONSTRAINT NAME.
De façon surprenante, la requête pour supprimer une contrainte est relativement différente de celle qui a permis de la créer. Plutôt que de faire un DROP CONSTRAINT, il faut faire un DROP FOREIGN KEY.
ALTER TABLE nom_table_avec_cle_etrangere
DROP FOREIGN KEY nom_contrainte;
Ainsi, pour supprimer la contrainte créée plus tôt, on entrera cette requête :
ALTER TABLE voitures
DROP FOREIGN KEY fk_voitures_clients;
« 14.1.18.3 Using FOREIGN KEY Constraints ». MySQL. http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html
« 14.5.6 InnoDB and FOREIGN KEY Constraints ». MySQL. http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html
« Les contraintes d'intégrité référentielle ». chicoree.fr. http://www.chicoree.fr/w/Les_contraintes_d%27int%C3%A9grit%C3%A9_r%C3%A9f%C3%A9rentielle
▼Publicité
