MySQL外鍵關聯操作如何實現

MySQL 的外鍵約束

注意，MySQL 的 InnoDB 表引擎才支援外鍵關聯，MyISAM 不支援。 SET FOREIGN_KEY_CHECKS = 0/1 可用於手動開啟或關閉 MySQL 的外鍵約束。

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外鍵關聯操作如何實現的詳細內容。更多資訊請關注PHP中文網其他相關文章！