Comment utiliser les contraintes de clés étrangères mysql

La contrainte de clé étrangère est utilisée pour établir une relation entre deux tables dans la base de données MySQL. C'est une caractéristique très importante de MySQL pour définir différents types de restrictions sur les tables connexes. Pour définir toute contrainte de clé étrangère pour tout tableau, vous devez utiliser la clé principale d'un autre tableau. La clé principale est une clé unique pour un tableau pour identifier la ligne particulière du tableau et lorsque cette clé primaire est utilisée dans une autre table pour faire une relation un à un ou un à plusieurs ou plusieurs à plusieurs est appelé une clé étrangère. Les caractéristiques des clés étrangères et comment ces clés peuvent être utilisées dans les tables MySQL sont présentées dans cet article.

Caractéristiques des contraintes de clés étrangères:

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 la même avec le type de données de la clé principale qui est utilisée dans la table parent pour référer la clé étrangère.
• Toute colonne d'index ou plusieurs colonnes peut être référencée comme une clé étrangère pour le tableau InNODB uniquement.
• Références Les privilèges ou au moins l'un des privilèges des instructions SELECT, INSERT, UPDATE et Supprimer sont nécessaires pour créer une clé étrangère.
• Une clé étrangère peut être créée de deux manières. Un en utilisant l'instruction CREATE et une autre en utilisant 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 parentale avec la clé principale. Supposons que le nom de la base de données soit 'bibliothèque'Et il contient deux tables de parent nommées'livres' et 'emprunteur'. Établir une connexion avec MySQL Server 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 une bibliothèque de bases de données;
Utiliser la bibliothèque;
Créer des livres de table (
id int not null auto_increment,
titre varchar (50) pas null,
Auteur Varchar (50) pas null,
éditeur varchar (50) pas null,
Clé primaire (ID)
) Moteur = inNODB;
Créer des emprunteurs de table (
id varchar (50) pas null,
nom varchar (50) pas null,
Adresse Varchar (50) pas nul,
Email Varchar (50) pas null,
Clé primaire (ID)
) Moteur = inNODB;

Définir la contrainte de clé étrangère à l'aide de la création de création

Créer une table nommée 'book_borrow_info'avec des contraintes de clés étrangères en exécutant la déclaration suivante. Ici le book_id Le champ est un clé étrangère pour ce tableau et chaque valeur de ce champ doit exister dans le identifiant domaine de livres tableau. livres est la table parent et book_borrow_info est la table des enfants. Deux restrictions sont également établies avec la clé étrangère ici. Ceux-ci sont Supprimer la cascade et Mettre à jour la cascade. Cela signifie que si une clé primaire supprime ou mettra à jour de la table parent, les enregistrements correspondants liés à l'enfant lié à la clé étrangère seront supprimés ou la clé étrangère sera mise à jour.

Créer une table book_borrow_info (
Borrow_id Varchar (50),
book_id int,
emprunter_date ne pas nul,
return_date date non nul,
statut varchar (15) pas null,
Index par_ind (book_id),
Clé primaire (emprunter_id, emprunter_date),
Clé étrangère (book_id) références aux livres (id)
Sur Supprimer Cascade
Sur la mise à jour Cascade
) Moteur = inNODB;

Maintenant, exécutez les instructions SQL suivantes pour insérer certains enregistrements dans les deux tables. La première instruction d'insertion inséra quatre enregistrements dans livres tableau. Les quatre valeurs de identifiant domaine de livres La table sera 1, 2, 3 et 4 pour l'attribut automatique. La deuxième instruction d'insertion inséra quatre enregistrements dans book_borrow_info basé sur identifiant valeur de livres tableau.

Insérer dans les valeurs des livres
(Null, «To Kill a Mockingbird», «Harper Lee», «Grand Central Publishing»),
(Null, «Cent ans de solitude», «Garcia Marquez», «Lutfi Ozkok»),
(Null, «A Passage to India», «Forster, E.M.',' BBC Hulton Picture Library '),
(Null, «Invisible Man», «Ralph Ellison», «Encyclopædia Britannica, Inc.');
Insérer dans les valeurs Book_Borrow_info
(«123490», 1, «2020-02-15», «2020-02-25», «retourné»),
(«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 du tableau parent, MySQL générera une erreur. L'instruction SQL suivante générera une erreur car le tableau parent, livres ne contient aucune valeur d'ID dix.

Insérer dans les valeurs Book_Borrow_info
(«195684», 10, «2020-04-15», «2020-04-30», «retourné»);

Après avoir exécuté l'instruction de suppression suivante, lorsque le quatrième enregistrement sera supprimé du livres Tableau puis les enregistrements connexes du book_borrow_info Le tableau sera supprimé automatiquement pour la contrainte de clé étrangère.

Supprimer des livres où id = 4;
Sélectionner * dans les livres;
SELECT * FROM Book_Brower_Info;

Définir la contrainte de clé étrangère à l'aide de la déclaration alter

Au début, insérer certains enregistrements dans emprunteurs table et ce tableau sera défini comme une table parentale dans la prochaine MODIFIER déclaration.

Insérer dans les valeurs des emprunteurs
('123490', 'Patrick Wood', '34 West Street Lancaster LA14 9ZH ',' Wood @ Gmail.com '),
('157643', 'Ezra Martin', '10 The Grove Birmingham B98 1EU ',' Martin @ hotmail.com '),
('174562', 'John Innes Archie', '55 Main Road Liverpool L2 3od ',' John @ Yahoo.com '),
('146788', 'Frederick Hanson', '85 Highfield Road Shrewsbury Sy46 3me ',' Hanson @ Gmail.com ');

Exécutez ce qui suit MODIFIER déclaration pour définir une autre contrainte de clé étrangère pour book_borrow_info table pour faire la relation avec emprunteurs tableau. Ici, emprunter est défini comme une clé étrangère pour book_borrow_info tableau.

ALTER TABLE Book_Borrow_info Ajouter une contrainte FK_BORORTHER
Clé étrangère (emprunter) références Emprunteurs (ID) sur Delete Cascade sur la mise à jour restreint;

Maintenant, insérez un record dans book_borrow_info avec valide emprunter valeur qui existe dans identifiant domaine de emprunteurs tableau. 157643 La valeur existe dans la table des emprunteurs et l'instruction d'insertion suivante sera exécutée avec succès.

Insérer dans les valeurs Book_Borrow_info
(«157643», 1, «2020-03-10», «2020-03-20», «retourné»);

L'instruction d'insertion suivante générera un message d'erreur car la valeur d'ID 195680 n'existe pas dans la table des emprunteurs.

Insérer dans les valeurs Book_Borrow_info
(«195680», 1, «2020-04-15», «2020-04-30», «retourné»);

Conclusion:

Définir correctement les contraintes de clés étrangères 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és étrangères est très essentielle 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és étrangères et à les appliquer correctement dans leurs tâches.