MySQL外鍵關聯操作的實現

Posted on by

MySQL 的外鍵約束

註意，MySQL 的 InnoDB 表引擎才支持外鍵關聯，MyISAM 不支持。MySQL 還支持手動打開或關閉外鍵約束：SET FOREIGN_KEY_CHECKS = 0/1;。

使用外鍵約束最大的好處在於 MySQL 幫助我們完成數據的一致性檢查。當我們使用默認的外鍵類型 RESTRICT 時，在創建、修改或者刪除記錄時都會檢查引用的合法性。

假設我們的數據庫中包含 posts(id, author_id, content) 和 authors(id, name) 兩張表，在執行如下所示的操作時都會觸發數據庫對外鍵的檢查：

向 posts 表中插入數據時，檢查 author_id 是否在 authors 表中存在；

修改 posts 表中的數據時，檢查 author_id 是否在 authors 表中存在；

刪除 authors 表中的數據時，檢查 posts 中是否存在引用當前記錄的外鍵；

作為專門用於管理數據的系統，數據庫與應用服務相比能夠更好地保證完整性，而上述的這些操作都是引入外鍵帶來的額外工作，不過這也是數據庫保證數據完整性的必要代價。上述的這些分析都是理論上的定性分析，我們其實可以簡單地定量分析一下引入外鍵對性能的影響。

創建表時定義外鍵（References，參照）

在 CREATE TABLE 語句中，通過 FOREIGN KEY 關鍵字來指定外鍵，具體的語法格式如下：

[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（從表），創建外鍵約束，讓 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;

NOTE：從表的外鍵關聯的必須是主表的主鍵，且主鍵和外鍵的數據類型必須一致。

以上語句執行成功之後，在表示 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)

修改原有表的外鍵約束

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

在修改數據表時添加外鍵約束的語法格式如下：

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

示例：修改數據表 tb_emp2，將字段 deptId 設置為外鍵，與數據表 tb_dept1 的主鍵 id 進行關聯。

# 創建 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

刪除外鍵約束

當一個表中不需要外鍵約束時，就需要從表中將其刪除。外鍵一旦刪除，就會解除主表和從表之間的關聯關系。

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

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

示例：刪除數據表 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外鍵關聯操作的實現的文章就介紹到這瞭,更多相關MySQL外鍵關聯操作內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：