basics mysql foreign key constraint with examples
Ce tutoriel explique les bases de la contrainte MySQL FOREIGN KEY, telles que sa syntaxe, comment l'ajouter, la déclarer, la supprimer et la modifier avec des exemples:
En termes très simples, la FOREIGN KEY est utilisée pour lier deux tables ou plus dans MySQL.
Les tables MySQL doivent être connectées afin d'interroger et de mettre à jour différents types de données à différents moments. Il est donc impératif d'avoir un point de liaison entre 2 tables.
Dans ce didacticiel, nous discuterons des différentes utilisations des clés étrangères et de la manière dont elles peuvent être déclarées et modifiées, ainsi que des contraintes qu'elles ont sur la structure globale de la table.
Ce que vous apprendrez:
Clé étrangère MySQL
Syntaxe:
CONSTRAINT {constraintName} FOREIGN KEY (referringColumnName) REFERENCES {referredTable}({referredColumn}) ON UPDATE {reference-option} ON DELETE {reference-option} Ci-dessus, la syntaxe utilisée lors de la spécification de FOREIGN KEY par rapport à une table lors de la création de la table ou avec Instruction ALTER TABLE.
Comprenons les différents composants de la syntaxe:
- constrantName: C'est le nom symbolique que nous voulons définir pour la contrainte FK spécifiée. Si cela est ignoré, le moteur MySQL attribue automatiquement un nom à la contrainte FK.
- referingColumnName: Il s'agit de la colonne qui ferait référence aux valeurs d'une autre table comme spécifié par la colonne dans la table référencée.
- Table référencée / table parent: Cela fait référence au nom de la table à partir de laquelle les valeurs seraient référencées.
- Colonne référencée: Le nom de la colonne dans la table référencée.
- Option de référence: Ce sont les actions qui entrent en jeu lorsqu'une action de mise à jour ou de suppression est effectuée sur la table contenant la contrainte de clé étrangère. UPDATE et DELETE peuvent avoir des options de référence identiques ou différentes.
Nous en apprendrons davantage sur les différentes actions d'intégrité référentielle plus loin dans ce didacticiel.
Voyons un exemple de référence FOREIGN KEY à l'aide de l'exemple Employee / Department. Nous allons créer une table Department avec des colonnes - departmentId (int & PRIMARY KEY) et departmentName (varchar).
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100));Créez une table Employé avec des colonnes comme ci-dessous:
| Colonne | Taper |
|---|---|
| identifiant | INT (clé primaire) |
| Nom | VARCHAR |
| dept_id | INT (clé étrangère) référencée à partir de la table des départements |
| adresse | VARCHAR |
| âge | INT |
| dob | DATE |
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE); Comme vous pouvez le voir, dans la table Employee ci-dessus, nous avons déclaré la colonne deptId de type Int et défini FOREIGN KEY à partir de la table Department sur la colonne departmentId.
Ce que cela signifie essentiellement que la colonne deptId de la table Employee ne peut contenir que les valeurs de la table Department.
Essayons d'insérer des données dans ces tableaux et voyons comment fonctionne FOREIGN KEY CONSTRAINT.
- Créez d'abord un enregistrement dans la table Service et ajoutez un enregistrement dans la table Employé faisant référence à l'ID de l'enregistrement qui a été ajouté à la table Service.
INSERT INTO department VALUES (1, 'ENGINEERING') --------- INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '1988-02-12',1); Vous verrez que les deux instructions seraient exécutées sans erreur.
- Référencez maintenant une valeur pour departmentId qui n'existe pas.
Par exemple, dans l'instruction de requête ci-dessous, nous créons un employé avec un departmentId non existant -10
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',10);- Dans ce scénario, nous obtiendrons une erreur comme ci-dessous:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)Ainsi, de manière générale, lorsque des références FOREIGN KEY sont définies, il est important de s’assurer que la table référencée doit contenir des données avant d’être référencée.
Actions d'intégrité référentielle
Essayons d'abord de comprendre ce qu'est exactement l'intégrité référentielle.
L'intégrité référentielle aide à maintenir les données dans un état propre et cohérent où des tables sont liées les unes aux autres avec une relation FOREIGN KEY.
En termes simples, l'intégrité référentielle fait référence à l'action que nous attendons du moteur de base de données, lorsqu'une UPDATE ou DELETE se produit dans la table référencée qui contient la clé étrangère.
Par exemple, dans notre exemple Employee / Department, supposons que nous modifions l'ID de service pour une certaine ligne dans DB. Ensuite, toutes les lignes de référence dans la table Employee seraient affectées. Nous pouvons définir différents types de scénarios d'intégrité référentielle qui pourraient être appliqués dans de tels cas.
Noter: L'intégrité référentielle est définie pendant la configuration / la déclaration FOREIGN KEY dans le cadre des commandes / sections ON DELETE et ON UPDATE.
Reportez-vous à un exemple de requête ici (pour l'exemple Employé / Service):
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100)); CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE {ref-integrity-action} ON DELETE {ref integrity action}); Insérez quelques données dans ces tableaux comme ci-dessous:
INSERT INTO department VALUES (1, 'ENGINEERING'), (2,'ACCOUNTING'), (3, 'MARKETING'), (4, 'HR'), (5, 'LEGAL'); INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32,'1988-02-12',1), (2, 'RYAN HILMAN', 'SEATTLE',43, '1977-03-15',1), (3, 'KAVITA SINGH', 'DELHI', 42, '1978-02-18',4), (4, 'DAVID BECKHAM', 'LONDON', 40, '1980-07-13',3), (5, 'PRITI KUMARI', 'DELHI', 35, '1985-12-11',2), (6, 'FRANK BALDING', 'NEW YORK', 35, '1985-08-25',5) Il existe 4 actions de référence prises en charge par MySQL. Essayons de comprendre chacun d’eux.
# 1) CASCADE
C'est l'une des actions d'intégrité référentielle les plus couramment utilisées. Définir DELETE et UPDATE sur CASCADE appliquerait les modifications apportées à la table référencée sur la table de référencement, c'est-à-dire dans l'exemple Employé / Département. Supposons que quelqu'un supprime une ligne dans la table Department en disant department_name = ACCOUNTING, alors toutes les lignes de la table Employee ayant department_id comme table Accounting seront également supprimées.
Comprenons cela avec un exemple:
SELECT * FROM employee;| identifiant | Nom | adresse | âge | dob | deptId |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
| deux | RYAN HILMAN | SEATTLE | 43 | 1977-03-15 | 1 |
| 3 | KAVITA SINGH | DELHI | 42 | 1978-02-18 | 4 |
| 4 | DAVID BECKHAM | LONDRES | 40 | 1980-07-13 | 3 |
| 5 | PRITI KUMARI | DELHI | 35 | 1985-12-11 | deux |
| 6 | FRANK BALDING | NEW YORK | 35 | 1985-08-25 | 5 |
Supprimer l'enregistrement de la table Department où departmentName = 'ACCOUNTING'
DELETE from DEPARTMENT WHERE departmentName='ACCOUNTING';Comme il s’agit d’une action de référence CASCADE, nous nous attendons à ce que toutes les lignes dont le departmentID = 2 (qui correspond au service «COMPTABILITÉ») soient également supprimées. Répétons une requête SELECT sur la table Employee.
SELECT * FROM employee;| identifiant | Nom | adresse | âge | dob | deptId |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
| deux | RYAN HILMAN | SEATTLE | 43 | 1977-03-15 | 1 |
| 3 | KAVITA SINGH | DELHI | 42 | 1978-02-18 | 4 |
| 4 | DAVID BECKHAM | LONDRES | 40 | 1980-07-13 | 3 |
| 6 | FRANK BALDING | NEW YORK | 35 | 1985-08-25 | 5 |
Comme vous pouvez le voir ci-dessus, en raison de l'intégrité référentielle CASCADE, les lignes de la table Employee qui faisaient référence à la colonne supprimée comme FOREIGN KEY auront ces lignes supprimées.
# 2) RESTRICT / PAS D'ACTION
Le mode RESTRICT ou NO ACTION n'autorisera aucune opération UPDATE ou DELETE sur la table dont les colonnes sont référencées comme FOREIGN KEY dans certaines tables.
Le mode NO ACTION peut être appliqué en omettant simplement les clauses ON UPDATE et ON DELETE de la déclaration de table.
Essayons le même exemple et, dans ce cas, ignorez simplement les actions d’intégrité référentielle ON UPDATE et ON DELETE.
Maintenant, lorsque nous essayons de supprimer une entrée dans la table référencée, nous obtiendrions une erreur car nous avons défini l'action référentielle sur RESTRICT
DELETE FROM department WHERE departmentName='ACCOUNTING';Vous verrez une erreur comme ci-dessous si vous essayez d'exécuter la commande DELETE ci-dessus.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`))# 3) SET NULL
Avec SET NULL, toute UPDATE ou DELETE dans la table référencée entraînerait la mise à jour d'une valeur NULL par rapport à la valeur de colonne marquée comme FOREIGN KEY dans la table de référence.
Avec cette action d'intégrité référentielle, la définition de la table Employé deviendrait la suivante:
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON DELETE SET NULL); Supprimez une ligne dans le tableau référencé comme indiqué ci-dessous:
DELETE FROM department WHERE departmentName='ACCOUNTING';Désormais, dans ce cas, la valeur référencée dans la table Employee serait définie sur NULL. Exécutez une requête SELECT sur la table Employee pour voir les résultats.
SELECT * FROM employee;| identifiant | Nom | adresse | âge | dob | deptId |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
| deux | RYAN HILMAN | SEATTLE | 43 | 1977-03-15 | 1 |
| 3 | KAVITA SINGH | DELHI | 42 | 1978-02-18 | 4 |
| 4 | DAVID BECKHAM | LONDRES | 40 | 1980-07-13 | 3 |
| 5 | PRITI KUMARI | DELHI | 35 | 1985-12-11 | NUL |
| 6 | FRANK BALDING | NEW YORK | 35 | 1985-08-25 | 5 |
# 4) RÉGLER PAR DÉFAUT
Le mode SET DEFAULT lorsqu'il est spécifié entraînerait la substitution de la valeur par défaut pour la colonne (comme spécifié lors de la déclaration de colonne), en cas de DELETES dans la table référencée.
comment ouvrir le fichier .java
Noter - Selon Documentation MySQL , l'option SET DEFAULT est prise en charge par MySQL Parser mais pas par les moteurs DB comme InnoDB. Cela pourrait être pris en charge à l'avenir.
Cependant, pour prendre en charge un tel comportement, vous pouvez envisager d'utiliser SET NULL et définir un déclencheur sur la table qui pourrait définir une valeur par défaut.
Ajouter une contrainte FOREIGN KEY à l'aide de l'instruction ALTER TABLE
Souvent, il peut arriver que nous souhaitons ajouter une contrainte FOREIGN KEY à une table existante qui ne l'a pas.
Supposons que dans l'exemple Employee and Department, nous ayons créé une table Employee sans aucune contrainte FOREIGN KEY et plus tard, nous souhaitons introduire la contrainte. Ceci peut être réalisé à l'aide de la commande ALTER TABLE.
Essayons de comprendre cela avec un exemple.
Supposons que nous ayons une table Employee avec la définition ci-dessous pour la commande CREATE.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);Ici, nous avons une colonne deptId mais pas de contrainte FOREIGN KEY. Dans ce cas, même sans avoir de table Department, nous pouvons spécifier toutes les valeurs lors de l'insertion d'enregistrements.
Maintenant, supposons plus tard que nous ayons une table Department distincte et que nous souhaitons y lier departmentId en tant que FOREIGN KEY à la table Employee.
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Et si ce tableau contient des données existantes? Pouvons-nous ALTER table et ajouter la contrainte FOREIGN KEY?
La réponse est oui - nous pouvons avec la condition que les valeurs existantes dans la colonne qui vont être référencées à partir d'une autre table doivent avoir ces valeurs existantes dans la table parent elle-même.
Créez une table Employee sans contrainte FOREIGN KEY, ajoutez des données et essayez d'ajouter une contrainte FOREIGN KEY à l'aide de la commande ALTER.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT); INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',1); INSERT INTO EMPLOYEE VALUES (2, 'ANITA SHERWIN', 'COLUMBIA', 32, '1988-02-12',10); Créez une table Department et ajoutez FOREIGN KEY dans le champ «deptId» dans la table Employee comme indiqué ci-dessous:
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100));À ce stade, si nous essayons d'ajouter la contrainte FOREIGN KEY,
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Ensuite, nous obtiendrons une erreur, car la table Employee contient des données mais la contrainte d'intégrité référentielle ne peut pas être satisfaite car la table Department ne contient pas encore de données.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`#sql-63_87`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)Pour avoir la contrainte FOREIGN KEY, nous devons d'abord ajouter des données à la table Department. Insérons les enregistrements requis dans le tableau Département.
INSERT INTO department VALUES (1, 'ENGINEERING'),(10,'ACCOUNTING');Ajoutez à nouveau la contrainte FOREIGN KEY en exécutant la même instruction ALTER TABLE. Vous remarquerez que cette fois, la commande réussit et la table Employee est mise à jour avec succès pour avoir deptId comme FOREIGN KEY de la table Department.
Suppression d'une contrainte FOREIGN KEY
Comme pour l'ajout d'une contrainte FOREIGN KEY, il est également possible de supprimer / supprimer une contrainte FOREIGN KEY existante d'une table.
Ceci peut être réalisé en utilisant la commande ALTER TABLE.
Syntaxe:
ALTER TABLE {childTable} DROP FOREIGN KEY {foreign key constraint name}; Ici «childTable» est le nom de la table qui a défini la contrainte FOREIGN KEY, tandis que le «nom de contrainte de clé étrangère» est le nom / symbole qui a été utilisé pour définir la FOREIGN KEY.
Voyons un exemple utilisant le tableau Employé / Service. Pour supprimer une contrainte nommée «depIdFk» de la table Employee, utilisez la commande ci-dessous:
ALTER TABLE employee DROP FOREIGN KEY depIdFk;Questions fréquemment posées
Q # 1) Comment puis-je changer les clés étrangères dans MySQL?
Répondre: FOREGIN KEY peut être ajouté / supprimé à l'aide de la commande ALTER TABLE.
Afin de modifier ou d'ajouter une nouvelle FOREIGN KEY, vous pouvez utiliser la commande ALTER et définir la FOREIGN KEY et la colonne de la table de référence qui seraient référencées à partir de la table enfant.
Q # 2) Comment définir plusieurs clés étrangères dans MySQL?
Répondre: Une table dans MySQL peut avoir plusieurs FOREIGN KEYS, qui peuvent dépendre de la même table parente ou de différentes tables parentes.
Utilisons la table Employee / Department et ajoutons FOREIGN KEY pour le nom du service ainsi que DepartmentId dans la table Employee.
Reportez-vous aux instructions CREATE des deux tableaux comme ci-dessous
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100) UNIQUE NOT NULL); ----xxxxx------xxxxx------xxxxx-------xxxxx------xxxxx CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, depName VARCHAR(100), CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT depNameFk FOREIGN KEY (depName) REFERENCES department(departmentName) ON UPDATE CASCADE ON DELETE CASCADE); Q # 3) Comment désactiver les contraintes de clé étrangère dans MySQL?
Répondre: Les contraintes FOREIGN KEY sont généralement requises lorsque quelqu'un tente de tronquer une table existante référencée. Pour ce faire, vous pouvez utiliser la commande ci-dessous:
SET FOREIGN_KEY_CHECKS=0;Cela définirait une variable de session et désactiverait temporairement FOREIGN_KEY_CHECKS. Après ce réglage, vous pouvez continuer et effectuer des suppressions / tronquer, ce qui n'aurait pas été possible autrement.
meilleur logiciel de sauvegarde pour windows 10 gratuit
Mais veuillez vous assurer qu'il s'agit d'un privilège d'administrateur et qu'il doit être utilisé judicieusement.
Q # 4) Comment trouver les références de clé étrangère pour une table dans MySQL?
Répondre: Pour lister toutes les contraintes FOREIGN KEY présentes, vous pouvez utiliser la table «INNODB_FOREIGN_COLS» dans «INFORMATION_SCHEMA».
Exécutez simplement la commande ci-dessous pour obtenir toutes les déclarations FOREIGN KEY existantes pour une instance de serveur MySQL donnée.
| identifiant | FOR_COL_NAME | REF_COL_NAME | POS |
|---|---|---|---|
| my_sql_foreign_key / depIdFk | deptId | DepartmentId | 1 |
Q # 5) La colonne référencée en tant que FOREIGN KEY doit-elle être une clé primaire dans la table référencée?
Répondre: Par définition de FOREIGN KEY, il serait nécessaire que la colonne référencée comme FOREIGN KEY soit la PRIMARY KEY de la table dans laquelle elle est référencée.
Cependant, avec les nouvelles versions de MySQL et avec le moteur de base de données InnoDB, vous pouvez également référencer une colonne qui a FOREIGN KEY qui a une contrainte UNIQUE et peut ne pas être nécessairement PRIMARY KEY.
Q # 6) Est-ce que FOREIGN KEY crée INDEX dans MySQL?
Répondre: Pour les contraintes de clé primaire et unique, MySQL crée automatiquement un INDEX pour ces colonnes.
Comme nous savons déjà que les références FOREIGN KEY ne peuvent être appliquées qu'à l'une ou l'autre des colonnes qui sont des clés primaires ou des colonnes qui ont des valeurs uniques, par conséquent, toutes les colonnes appelées FOREIGN KEY ont un index créé par rapport à elles.
Afin d'afficher l'index sur une table, utilisez la commande ci-dessous:
SHOW INDEX from {dbName.tableName};Ainsi, pour notre exemple Employee / Department, nous avons ajouté deptId dans Employee en tant que FOREIGN KEY de la table Department.
Voyons les index créés dans les tables Employee et Department.
USE my_sql_foreign_key; SHOW INDEX from employee; | Table | Non_unique | Nom_clé | Seq_in_index | Nom de colonne | Collation | Cardinalité | Sous_part | Emballé | Nul | Index_type |
|---|---|---|---|---|---|---|---|---|---|---|
| employé | 0 | PRIMAIRE | 1 | identifiant | À | 0 | NUL | NUL | BTREE | |
| employé | 1 | depIdFk | 1 | deptId | À | 0 | NUL | NUL | OUI | BTREE |
Vous pouvez voir 2 index - l'un est la clé primaire pour la table Employee et un autre pour FOREIGN KEY depId qui est référencé à partir de la table Department.
SHOW INDEX from department;| Table | Non_unique | Nom_clé | Seq_in_index | Nom de colonne | Collation | Cardinalité | Sous_part | Emballé | Nul | Index_type |
|---|---|---|---|---|---|---|---|---|---|---|
| département | 0 | PRIMAIRE | 1 | DepartmentId | À | 0 | NUL | NUL | BTREE |
Ici, vous pouvez voir que pour la table Department, nous n'avons qu'un seul index pour la clé primaire (qui est référencée comme FOREIGN KEY dans la table Employee).
Q # 7) FOREIGN KEY peut-il être NULL dans MySQL?
Répondre: Oui, il est parfaitement normal d’avoir NULL pour la colonne qui a une dépendance FOREIGN KEY sur une autre table. Cela fait également allusion au fait que NULL n'est pas une valeur réelle et qu'il n'est donc pas mis en correspondance / comparé aux valeurs de la table parente.
Conclusion
Dans ce didacticiel, nous avons découvert différents concepts liés à l'utilisation de FOREIGN KEYS dans les bases de données MySQL.
FOREIGN KEY facilite les mises à jour et les suppressions avec des restrictions appropriées, mais parfois avoir beaucoup de telles relations peut rendre tout le processus d'insertion et / ou de suppression assez fastidieux.
Nous avons appris comment créer FOREIGN KEYS et comment nous pouvons mettre à jour et supprimer une FOREIGN KEY existante de la table enfant. Nous avons également découvert différentes actions d'intégrité référentielle et comment nous pouvons obtenir différents comportements en utilisant les différentes options disponibles comme CASCADE, NO ACTION, SET NULL, etc.
lecture recommandée
- Tutoriel MySQL Create Table avec des exemples
- MySQL Insert Into Table - Syntaxe et exemples d'instructions d'insertion
- Tutoriel MySQL Create View avec des exemples de code
- Fonctions MySQL CONCAT et GROUP_CONCAT avec exemples
- Tutoriel de transaction MySQL avec des exemples de programmation
- MySQL UNION - Tutoriel complet avec des exemples d'Union
- Comment télécharger MySQL pour Windows et Mac
- Différence entre SQL et MySQL et SQL Server (avec exemples)