Comment implémenter l'opération d'association de clé étrangère MySQL

Contraintes de clé étrangère de MySQL

Notez que seul le moteur de table InnoDB de MySQL prend en charge les associations de clés étrangères, contrairement à MyISAM. SET FOREIGN_KEY_CHECKS = 0/1 peut être utilisé pour activer ou désactiver manuellement les contraintes de clé étrangère de MySQL.

Le plus grand avantage des contraintes de clé étrangère de MySQL est qu'elles peuvent nous aider à effectuer la vérification de la cohérence des données. En utilisant le type de clé étrangère RESTRICT par défaut, des contrôles de validité des références seront effectués lors de la création, de la modification ou de la suppression d'enregistrements.

Supposons que notre base de données contienne deux tables : posts(id, author_id, content) et authors(id, name). Lors de l'exécution des opérations suivantes, la base de données déclenchera la vérification des clés étrangères :

Insérer dans la table posts Lors de la suppression. data, vérifiez si l'author_id existe dans la table des auteurs ;

Lors de la modification des données dans la table des publications, vérifiez si l'author_id existe dans la table des auteurs ;

Lors de la suppression des données dans la table des auteurs, vérifiez s'il y a une référence à l'enregistrement actuel dans les messages Clés étrangères ;

En tant que système spécifiquement utilisé pour gérer les données, la base de données peut mieux garantir l'intégrité par rapport aux services d'application. Les opérations ci-dessus sont toutes un travail supplémentaire causé par l'introduction de clés étrangères, mais c'est aussi un travail supplémentaire. la garantie des données par la base de données Le prix nécessaire de l'intégrité. Nous pouvons mener une simple analyse quantitative pour comprendre l’impact spécifique de l’introduction de clés étrangères sur les performances, plutôt qu’une simple analyse qualitative théorique.

Définissez les clés étrangères (Références) lors de la création d'une table

Dans l'instruction CREATE TABLE, spécifiez la clé étrangère via le mot-clé FOREIGN KEY Le format de syntaxe spécifique est le suivant :

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

Exemple :

# 部门表 tb_dept1（主表）
CREATE TABLE tb_dept1
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) NOT NULL,
    location VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
 
# 员工表 tb_emp6（从表），创建外键约束，让 deptId 作为外键关联到 tb_dept1 的主键 id。
CREATE TABLE tb_emp6
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT,
    CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

REMARQUE : À partir du. table La clé étrangère doit être liée à la clé primaire de la table principale, et les types de données de la clé primaire et de la clé étrangère doivent être cohérents.

Une fois l'instruction ci-dessus exécutée avec succès, une contrainte de clé étrangère nommée fk_emp_dept1 est ajoutée à la représentation tb_emp6. Le nom de la clé étrangère est deptId, qui dépend de l'identifiant de clé primaire de la table tb_dept1.

Afficher les informations de contrainte de la table principale

MariaDB [test_db]> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME='tb_dept1'\G;
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test_db
              CONSTRAINT_NAME: fk_emp_dept1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test_db
                   TABLE_NAME: tb_emp6
                  COLUMN_NAME: deptId
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: test_db
        REFERENCED_TABLE_NAME: tb_dept1
       REFERENCED_COLUMN_NAME: id
1 row in set (0.00 sec)

Modifier les contraintes de clé étrangère de la table d'origine

Des contraintes de clé étrangère peuvent également être ajoutées lors de la modification de la table, mais la condition préalable à l'ajout de contraintes de clé étrangère est : les données dans le La colonne de clé étrangère de la table doit être cohérente avec les données de la colonne de clé primaire de la table principale ou il n'y a pas de données.

Le format de syntaxe pour ajouter des contraintes de clé étrangère lors de la modification de la table de données est le suivant :

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

Exemple : modifiez la table de données tb_emp2, définissez le champ deptId comme clé étrangère et associez-le à l'identifiant de clé primaire des données table tb_dept1.

# 创建 tb_emp2（从表）
CREATE TABLE tb_emp2
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
 
MariaDB [test_db]> desc tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
 
# 添加外键约束
ALTER TABLE tb_emp2 ADD CONSTRAINT fk_tb_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id);
 
MariaDB [test_db]> desc tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
 
MariaDB [test_db]> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

Supprimer les contraintes de clé étrangère

Lorsqu'une contrainte de clé étrangère n'est plus nécessaire dans une table, elle doit être supprimée de la table. Une fois la clé étrangère supprimée, l'association entre la table maître et la table esclave sera libérée.

Le format de syntaxe pour supprimer une contrainte de clé étrangère est le suivant :

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

Exemple : Supprimez la contrainte de clé étrangère fk_tb_dept1 dans la table de données tb_emp2.

ALTER TABLE tb_emp2 DROP FOREIGN KEY fk_tb_dept1;
 
MariaDB [test_db]> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

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!