Caractéristiques des contraintes de clé étrangère :
Certaines caractéristiques importantes de la contrainte de clé étrangère sont expliquées ci-dessous.
- Le type de données de la clé étrangère utilisée dans la table enfant doit être le même que le type de données de la clé primaire utilisée dans la table parent pour faire référence à la clé étrangère.
- Toute colonne d'index ou plusieurs colonnes peuvent être référencées en tant que clé étrangère pour la table InnoDB uniquement.
- Les privilèges de références ou au moins l'un des privilèges des instructions SELECT, INSERT, UPDATE et DELETE sont requis pour créer une clé étrangère.
- Une clé étrangère peut être créée de deux manières. L'un à l'aide de l'instruction CREATE et l'autre à l'aide de l'instruction ALTER.
Prérequis:
Avant de créer une contrainte de clé étrangère, vous devez créer une base de données et une table parent avec la clé primaire. Supposons que le nom de la base de données est ' une bibliothèque ' et il contient deux tables parent nommées ' livres ' et ' emprunteur '. Établissez une connexion avec le serveur MySQL en utilisant le mysql client et exécutez les instructions SQL suivantes pour créer la base de données et les tables.
CRÉER BASE DE DONNÉES une bibliothèque;
UTILISATION une bibliothèque;
CRÉER TABLE livres(
identifiant INT NE PAS NUL INCRÉMENTATION AUTOMATIQUE ,
Titre varchar (cinquante) NE PAS NUL ,
auteur varchar (cinquante) NE PAS NUL ,
éditeur varchar (cinquante) NE PAS NUL ,
CLÉ PRIMAIRE (identifiant)
) MOTEUR = INNODB ;
CRÉER TABLE emprunteurs(
identifiant VARCHAR (cinquante) NE PAS NUL ,
Nom varchar (cinquante) NE PAS NUL ,
adresse varchar (cinquante) NE PAS NUL ,
e-mail varchar (cinquante) NE PAS NUL ,
CLÉ PRIMAIRE (identifiant)
) MOTEUR = INNODB ;
Définir la contrainte de clé étrangère à l'aide de l'instruction CREATE
Créez une table nommée ' book_borrow_info ' avec des contraintes de clé étrangère en exécutant l'instruction suivante. Ici le id_livre le champ est un clé étrangère pour cette table et chaque valeur de ce champ doit exister dans le identifiant domaine de livres table. livres est la table parente et book_borrow_info est la table enfant. Deux restrictions sont également définies avec la clé étrangère ici. Ceux-ci sont SUPPRIMER CASCADE et CASCADE DE MISE À JOUR . Cela signifie que si une clé primaire est supprimée ou mise à jour de la table parent, les enregistrements correspondants liés à la table enfant liée à la clé étrangère seront supprimés ou la clé étrangère sera mise à jour.
CRÉER TABLE book_borrow_info(
emprunt_id VARCHAR (cinquante),
id_livre INT ,
date_d'emprunt DATE NE PAS NUL ,
date de retour DATE NE PAS NUL ,
statut VARCHAR (quinze) NE PAS NUL ,
INDICE par_ind(id_livre),
CLÉ PRIMAIRE (id_emprunt,date_d'emprunt),
CLÉ ÉTRANGÈRE (id_livre) LES RÉFÉRENCES livres(identifiant)
AU EFFACER CASCADE
AU METTRE À JOUR CASCADE
) MOTEUR = INNODB ;
Maintenant, exécutez les instructions SQL suivantes pour insérer des enregistrements dans les deux tables. La première instruction INSERT insère quatre enregistrements dans livres table. Les quatre valeurs de identifiant domaine de livres table sera 1, 2, 3 et 4 pour l'attribut d'auto-incrémentation. La deuxième instruction INSERT insère quatre enregistrements dans book_borrow_info basé sur identifiant valeur de livres table.
INSÉRER DANS livres VALEURS
( NUL , 'Tuer un oiseau moqueur', 'Harper Lee', « Édition Grand Central »),
( NUL , 'Cent ans de solitude', 'Garcia Márquez', 'Lutfi Ozkok'),
( NUL , 'Un passage vers l'Inde', 'Forster, E.M.', 'BBC Hulton Picture Library'),
( NUL , 'Homme invisible', 'Ralph Ellison', 'Encyclopædia Britannica, Inc.');
INSÉRER DANS book_borrow_info VALEURS
('123490', 1, '2020-02-15', '2020-02-25', 'Revenu'),
('157643', 2, '2020-03-31', '2020-03-10', 'En attente'),
('174562', 4, '2020-04-04', '2020-04-24', 'Emprunté'),
('146788', 3, '2020-04-10', '2020-01-20', 'Emprunté');
Si vous essayez d'insérer une valeur dans le champ de clé étrangère de la table enfant qui n'existe pas dans le champ de clé primaire de la table parent, MySQL générera une erreur. L'instruction SQL suivante générera une erreur car la table parent, livres ne contient aucune valeur d'identification dix .
INSÉRER DANS book_borrow_info VALEURS('195684', dix, '2020-04-15', '2020-04-30', 'Revenu');
Après avoir exécuté l'instruction DELETE suivante, lorsque le quatrième enregistrement sera supprimé du livres table puis les enregistrements associés de la book_borrow_info table sera supprimée automatiquement pour la contrainte de clé étrangère.
EFFACER DE livres OÙ identifiant= 4;SÉLECTIONNER * de livres;
SÉLECTIONNER * de book_borrow_info;
Définir la contrainte de clé étrangère à l'aide de l'instruction ALTER
Au début, insérez quelques enregistrements dans emprunteurs table et cette table sera définie comme table parente dans la prochaine ÂGE déclaration.
INSÉRER DANS emprunteurs VALEURS('123490', 'Patrick Bois', '34 West Street LANCASTER LA14 9ZH', ' [email protégé] '),
('157643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU', ' [email protégé] '),
('174562', 'John Innes Archie', '55 Main Road LIVERPOOL L2 3OD', ' [email protégé] '),
('146788', « Frédéric Hanson », '85 Highfield Road SHREWSBURY SY46 3ME', ' [email protégé] ');
Exécutez ce qui suit ÂGE instruction pour définir une autre contrainte de clé étrangère pour book_borrow_info table pour faire la relation avec emprunteurs table. Ici, emprunt_id est défini comme une clé étrangère pour book_borrow_info table.
ALTER TABLE book_borrow_info ADD CONSTRAINT fk_borrowerCLÉ ÉTRANGÈRE(emprunt_id)RÉFÉRENCES emprunteurs(identifiant)SUR SUPPRIMER CASCADE SUR MISE À JOUR RESTREINDRE ;
Maintenant, insérez un enregistrement dans book_borrow_info avec valide emprunt_id valeur qui existe dans identifiant domaine de emprunteurs table. 157643 value existe dans la table des emprunteurs et l'instruction INSERT suivante sera exécutée avec succès.
INSÉRER DANS book_borrow_info VALEURS('157643', 1, '2020-03-10', '2020-03-20', 'Revenu');
L'instruction INSERT suivante générera un message d'erreur car la valeur de l'identifiant 195680 n'existe pas dans la table des emprunteurs.
INSÉRER DANS book_borrow_info VALEURS('195680', 1, '2020-04-15', '2020-04-30', 'Revenu');
Conclusion:
Définir correctement les contraintes de clé étrangère est une tâche très importante pour créer une base de données relationnelle et gérer les données entre les tables de manière appropriée. Connaître les utilisations des contraintes de clé étrangère est essentiel pour les concepteurs de bases de données. J'espère que cet article aidera les nouveaux concepteurs de bases de données à comprendre le concept de contraintes de clé étrangère et à les appliquer correctement dans leurs tâches.