MySQL外來鍵關聯操作的實現

2022-07-28 22:05:37

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_emp2G
*************************** 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_emp2G
*************************** 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外來鍵關聯操作內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL外來鍵關聯操作的實現

目錄

MySQL 的外來鍵約束

修改原有表的外來鍵約束

刪除外來鍵約束

熱門文章