MySQL ne prend pas en charge les clés étrangères

mysql prend en charge les clés étrangères. Dans MySQL, les clés étrangères sont principalement utilisées pour établir la relation entre la table maître et la table esclave. Elles peuvent établir une connexion pour les données des deux tables et limiter la cohérence et l'intégrité des données dans les deux tables lorsque la table maître est utilisée. La table supprime un enregistrement. L'enregistrement correspondant de la table doit également être modifié en conséquence. Une table peut avoir une ou plusieurs clés étrangères, et la clé étrangère peut être une valeur nulle. Si ce n'est pas une valeur nulle, la valeur de chaque clé étrangère doit être égale à une certaine valeur de la clé primaire dans la table principale ; et le nombre de colonnes dans la clé étrangère et le type de données correspondant doit être le même que la clé primaire de la table principale.

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

mysql prend en charge les clés étrangères.

MySQL FOREIGN KEY (FOREIGN KEY)

Une clé étrangère est un champ dans une table spécifiée qui correspond à un autre champ dans une autre table. Les clés étrangères définissent des contraintes sur les données des tables associées, ce qui permet à MySQL de maintenir l'intégrité référentielle.

Les clés étrangères sont utilisées pour établir la relation entre la table maître et la table esclave, établir une connexion pour les données des deux tables et contraindre la cohérence et l'intégrité des données dans les deux tables.

Pour deux tables avec une relation associée, la table où se trouve la clé primaire du champ associé est la table principale (table parent), et la table où se trouve la clé étrangère est la table secondaire (table enfant).

Lorsqu'un enregistrement est supprimé de la table principale, l'enregistrement correspondant de la table doit également être modifié en conséquence. Une table peut avoir une ou plusieurs clés étrangères, et la clé étrangère peut être nulle. Si elle n'est pas nulle, la valeur de chaque clé étrangère doit être égale à une certaine valeur de la clé primaire de la table principale.

Jetons un coup d'œil au diagramme de base de données suivant à partir de l'exemple de base de données.

Nous avons deux tables : les clients et les commandes. Chaque client a zéro ou plusieurs commandes, et chaque commande ne peut appartenir qu'à un seul client. La relation entre la table clients et la table commandes est de un à plusieurs, les commandes étant établies par la clé étrangère dans la table spécifiée par le champ customerNumber. Le champ customerNumber de la table clients est lié au champ de clé primaire customerNumber de la table des commandes.

La table des clients est appelée table parent ou table de référence, et la table des commandes est appelée table enfant ou table de référence.

La clé étrangère peut être une colonne ou un ensemble de colonnes. Les colonnes de la table enfant font généralement référence aux colonnes de clé primaire de la table parent.

Les tables peuvent avoir plusieurs clés étrangères, et chaque clé étrangère d'une table enfant peut faire référence à une table parent différente.

Les lignes de la table enfant doivent contenir des valeurs qui existent dans la table parent, par exemple, chaque enregistrement de commande dans la table des commandes doit avoir une valeur qui existe dans la table clients customerNumber. Par conséquent, plusieurs commandes peuvent référencer le même client, et cette relation est appelée un (client) à plusieurs (commandes) ou un à plusieurs.

Parfois, la table enfant et la table parent sont identiques. La clé étrangère fait référence à la clé primaire de la table, par exemple, sous la table des employés :

La colonne reportTo est une clé étrangère, qui fait référence à EmployeeNumber comme colonne de la clé primaire de la table des employés pour refléter la structure hiérarchique entre les employés, c'est-à-dire que chaque employé relève d'un autre employé, un employé peut avoir zéro ou plusieurs subordonnés directs. Nous proposons un didacticiel sur les auto-jointures qui peut vous aider à interroger des données sur de telles tables. La clé étrangère

reportTo est également connue sous le nom de clé étrangère récursive ou auto-référentielle.

Les clés étrangères renforcent l'intégrité référentielle, vous aidant à maintenir automatiquement la cohérence et l'intégrité de vos données. Par exemple, vous ne pouvez pas créer une commande pour un client qui n'existe pas.

De plus, vous pouvez configurer une cascade sur l'opération de suppression de la clé étrangère customerNumber afin que lorsqu'un client est supprimé dans la table clients, toutes les commandes associées au client soient également supprimées. Cela permet d'économiser du temps et des efforts en utilisant plusieurs instructions DELETE ou DELETE JOIN.

Comme pour la suppression, vous pouvez également définir une cascade pour la clé étrangère customerNumber lors de l'opération de mise à jour afin d'effectuer des mises à jour de tables croisées sans utiliser plusieurs instructions UPDATE ou UPDATE JOIN.

Remarque : Dans MySQL, le moteur de stockage InnoDB prend en charge les clés étrangères, vous devez donc créer une table InnoDB pour utiliser les contraintes de clé étrangère.

MySQL doit respecter les règles suivantes lors de la définition des clés étrangères :

• La table principale doit déjà exister dans la base de données, ou être la table en cours de création. Si c'est le dernier cas, la table maître et la table esclave sont la même table. Une telle table est appelée table auto-référentielle, et cette structure est appelée intégrité auto-référentielle.

• Doit définir une clé primaire pour la table principale.

• Les clés primaires ne peuvent pas contenir de valeurs nulles, mais les valeurs nulles sont autorisées dans les clés étrangères. Autrement dit, tant que chaque valeur non nulle de la clé étrangère apparaît dans la clé primaire spécifiée, le contenu de la clé étrangère est correct.

• Spécifiez le nom de la colonne ou une combinaison de noms de colonnes après le nom de la table principale. Cette colonne ou combinaison de colonnes doit être la clé primaire ou la clé candidate de la table primaire.

• Le nombre de colonnes dans la clé étrangère doit être le même que le nombre de colonnes dans la clé primaire de la table principale.

• Le type de données de la colonne dans la clé étrangère doit être le même que le type de données de la colonne correspondante dans la clé primaire de la table principale.

Création d'une clé étrangère dans une table

Syntaxe de création de clé étrangère MySQL

La syntaxe suivante explique comment définir une clé étrangère dans une table enfant dans une instruction CREATE TABLE.

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

Apprenons la syntaxe plus en détail :

• La clause CONSTRAINT vous permet de définir des noms de contraintes pour les contraintes de clé étrangère. Si vous l'omettez, MySQL générera automatiquement un nom.

• Clause FOREIGN KEY spécifie les colonnes de la table enfant qui font référence aux colonnes de clé primaire de la table parent. Vous pouvez mettre un nom de clé étrangère après la clause FOREIGN KEY ou laisser MySQL créer un nom pour vous. Notez que MySQL crée automatiquement l'index en utilisant le nom Foreign_key_name.

• La clause REFERENCES spécifie la table parent et les colonnes référencées par les colonnes de la table enfant. Le nombre de colonnes dans la table enfant et la table parent spécifiées FOREIGN KEY et REFERENCES doivent être identiques.

• La clause ON DELETE vous permet de définir ce qui est enregistré dans la table enfant lorsque les enregistrements de la table parent sont supprimés. Si vous omettez la clause ON DELETE et supprimez les enregistrements de la table parent qui contiennent des enregistrements dans la table enfant, MySQL refusera de supprimer. De plus, MySQL vous propose également des actions vous permettant d'utiliser des options supplémentaires telles que ON DELETE CASCADE , qui demande à MySQL de supprimer les enregistrements de la table enfant, et lorsqu'un enregistrement de la table parent est supprimé, l'enregistrement fera référence à l'enregistrement dans la table parent. Si vous ne souhaitez pas supprimer les enregistrements associés dans la table enfant, utilisez plutôt l'opération ON DELETE SET NULL. MySQL définira la valeur de la colonne de clé étrangère dans la table enfant sur NULL lors de la suppression d'enregistrements dans la table parent, la condition est que la colonne de clé étrangère dans la table enfant doit accepter les valeurs NULL. Notez que MySQL rejettera la suppression si vous utilisez l'opération ON DELETE NO ACTION ou ON DELETE RESTRICT.

• La clause ON UPDATE vous permet de spécifier ce qui arrive aux lignes de la table enfant lorsque les lignes de la table parent sont mises à jour. Vous pouvez omettre la clause ON UPDATE pour que MySQL rejette toute mise à jour des lignes de la table enfant lors de la mise à jour des lignes de la table parent. L'opération ON UPDATE CASCADE vous permet d'effectuer des mises à jour entre tables croisées, et lorsqu'une ligne de la table parent est mise à jour ON UPDATE SET NULL, l'opération réinitialise la valeur de la ligne de la table enfant à la valeur NULL. ON UPDATE NO ACTION ou UPDATE RESTRICT action pour rejeter toute mise à jour.

Exemple de clé étrangère de création de table MySQL

L'exemple suivant crée une base de données dbdemo et deux tables : catégories et produits. Chaque catégorie contient un ou plusieurs produits et chaque produit appartient à une seule catégorie. Le champ cat_id dans la table products est défini comme une clé étrangère avec les opérations UPDATE ON CASCADE et DELETE ON RESTRICT.

CREATE DATABASE IF NOT EXISTS dbdemo;
 
USE dbdemo;
 
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
 
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

Ajouter une clé étrangère à la table

MySQL ajoute une syntaxe de clé étrangère

Pour ajouter une clé étrangère à une table existante, utilisez l'instruction ALTER TABLE avec la syntaxe de définition de clé étrangère ci-dessus :

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;

Exemple d'ajout de clé étrangère MySQL

Maintenant, ajoutons une nouvelle table appelée vendeurs et modifions la table des produits pour inclure le champ ID du fournisseur :

USE dbdemo;
 
CREATE TABLE vendors(
    vdr_id int not null auto_increment primary key,
    vdr_name varchar(255)
)ENGINE=InnoDB;
 
ALTER TABLE products 
ADD COLUMN vdr_id int not null AFTER cat_id;

Pour ajouter les produits de clé étrangère à la table, utilisez l'instruction suivante :

ALTER TABLE products
ADD FOREIGN KEY fk_vendor(vdr_id)
REFERENCES vendors(vdr_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

Maintenant, la table des produits a deux clés étrangères, l'une fait référence à la table des catégories et l'autre fait référence à la table des fournisseurs.

【Recommandation associée : tutoriel vidéo mysql】

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!