MySQL外鍵關(guān)聯(lián)操作如何實(shí)現(xiàn)

本文小編為大家詳細(xì)介紹“MySQL外鍵關(guān)聯(lián)操作如何實(shí)現(xiàn)”，內(nèi)容詳細(xì)，步驟清晰，細(xì)節(jié)處理妥當(dāng)，希望這篇“MySQL外鍵關(guān)聯(lián)操作如何實(shí)現(xiàn)”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來(lái)學(xué)習(xí)新知識(shí)吧。

MySQL 的外鍵約束

注意，MySQL 的 InnoDB 表引擎才支持外鍵關(guān)聯(lián)，MyISAM 不支持。MySQL 還支持手動(dòng)打開或關(guān)閉外鍵約束：SET FOREIGN_KEY_CHECKS = 0/1;。

使用外鍵約束最大的好處在于 MySQL 幫助我們完成數(shù)據(jù)的一致性檢查。當(dāng)我們使用默認(rèn)的外鍵類型 RESTRICT 時(shí)，在創(chuàng)建、修改或者刪除記錄時(shí)都會(huì)檢查引用的合法性。

假設(shè)我們的數(shù)據(jù)庫(kù)中包含 posts(id, author_id, content) 和 authors(id, name) 兩張表，在執(zhí)行如下所示的操作時(shí)都會(huì)觸發(fā)數(shù)據(jù)庫(kù)對(duì)外鍵的檢查：

向 posts 表中插入數(shù)據(jù)時(shí)，檢查 author_id 是否在 authors 表中存在；

修改 posts 表中的數(shù)據(jù)時(shí)，檢查 author_id 是否在 authors 表中存在；

刪除 authors 表中的數(shù)據(jù)時(shí)，檢查 posts 中是否存在引用當(dāng)前記錄的外鍵；

作為專門用于管理數(shù)據(jù)的系統(tǒng)，數(shù)據(jù)庫(kù)與應(yīng)用服務(wù)相比能夠更好地保證完整性，而上述的這些操作都是引入外鍵帶來(lái)的額外工作，不過這也是數(shù)據(jù)庫(kù)保證數(shù)據(jù)完整性的必要代價(jià)。上述的這些分析都是理論上的定性分析，我們其實(shí)可以簡(jiǎn)單地定量分析一下引入外鍵對(duì)性能的影響。

創(chuàng)建表時(shí)定義外鍵（References，參照）

在 CREATE TABLE 語(yǔ)句中，通過 FOREIGN KEY 關(guān)鍵字來(lái)指定外鍵，具體的語(yǔ)法格式如下：

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

示例：

# 部門表 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（從表），創(chuàng)建外鍵約束，讓 deptId 作為外鍵關(guān)聯(lián)到 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;

NOTE：從表的外鍵關(guān)聯(lián)的必須是主表的主鍵，且主鍵和外鍵的數(shù)據(jù)類型必須一致。

以上語(yǔ)句執(zhí)行成功之后，在表示 tb_emp6 上添加了名稱為 fk_emp_dept1 的外鍵約束，外鍵名稱為 deptId，其依賴于表 tb_dept1 的主鍵 id。

查看主表的約束信息

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)

修改原有表的外鍵約束

外鍵約束也可以在修改表時(shí)添加，但是添加外鍵約束的前提是：從表中外鍵列中的數(shù)據(jù)必須與主表中主鍵列中的數(shù)據(jù)一致或者是沒有數(shù)據(jù)。

在修改數(shù)據(jù)表時(shí)添加外鍵約束的語(yǔ)法格式如下：

ALTER TABLE <數(shù)據(jù)表名> ADD CONSTRAINT <外鍵名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

示例：修改數(shù)據(jù)表 tb_emp2，將字段 deptId 設(shè)置為外鍵，與數(shù)據(jù)表 tb_dept1 的主鍵 id 進(jìn)行關(guān)聯(lián)。

# 創(chuàng)建 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

刪除外鍵約束

當(dāng)一個(gè)表中不需要外鍵約束時(shí)，就需要從表中將其刪除。外鍵一旦刪除，就會(huì)解除主表和從表之間的關(guān)聯(lián)關(guān)系。

刪除外鍵約束的語(yǔ)法格式如下所示：

ALTER TABLE <表名> DROP FOREIGN KEY <外鍵約束名>;

示例：刪除數(shù)據(jù)表 tb_emp2 中的外鍵約束 fk_tb_dept1。

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

讀到這里，這篇“MySQL外鍵關(guān)聯(lián)操作如何實(shí)現(xiàn)”文章已經(jīng)介紹完畢，想要掌握這篇文章的知識(shí)點(diǎn)還需要大家自己動(dòng)手實(shí)踐使用過才能領(lǐng)會(huì)，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注億速云行業(yè)資訊頻道。