MySQL DDL操作--------外鍵最佳實戰

標籤：mysql   dba   外鍵約束   

1. 背景

   * MySQL有兩種常用的引擎類型MyISAM和InnoDB。目前只有InnoDB引擎類型支援外鍵約束。

   * 本表的列必須與外鍵類型相同, 外鍵必須是外表的主鍵

   * 設定外建的列不能設定 NO NULL 欄位屬性。

2. 外建作用

   * 使兩張表形成關聯,外鍵只能引用外表中的列的值

   * 保持資料一致性，完整性，控制儲存在外鍵表中的資料

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M02/9A/8E/wKiom1lXWFahYAJAAADRovi0L_w011.png" title="外鍵.png" alt="wKiom1lXWFahYAJAAADRovi0L_w011.png" />

3. 外鍵實驗 [ 員工 --> 部門 ]

   * 建立外鍵依賴的外表 departments

mysql> CREATE TABLE departments(    -> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,    -> name VARCHAR(64) NOT NULL    -> )ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.05 sec)

   * 建立員工表 empoyees, 並建立外鍵

     指定外鍵關鍵字： FOREIGN KEY(列名) 

     引用外鍵關鍵字： REFERENCES <外鍵表名>(外鍵列名)　

     事件觸發限制:  [預設] no action

             ON DELETE  SET NULL   當外表欄位刪除時，本表設定為NULL(空值) [ 不推薦設定此薦 ]

        ON UPDATE CASCADE   當外表欄位更新時，本表外鍵串聯更新

             ON DELETE/UPDATE SET DEFAULT 當有事件觸發時，設定為預設值

　　　　ON DELETE/UPDATE RESTRICT      當有事件觸發時，限制外表中的外鍵改動

mysql> CREATE TABLE empoyees(     -> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,    -> sex ENUM(‘M‘, ‘F‘) NOT NULL,    -> age INT NOT NULL,    -> department BIGINT,    -> FOREIGN KEY(department)     -> REFERENCES departments(id)     -> ON DELETE SET NULL    -> ON UPDATE CASCADE    -> )ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.12 sec)

   * 對部門表 departments 插入資料

mysql> INSERT INTO departments SELECT NULL, ‘dev‘;Query OK, 1 row affected (0.01 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> INSERT INTO departments SELECT NULL, ‘test‘;Query OK, 1 row affected (0.01 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> INSERT INTO departments SELECT NULL, ‘ops‘;Query OK, 1 row affected (0.01 sec)Records: 1  Duplicates: 0  Warnings: 0

   * 查看部門表資料

mysql> SELECT * FROM departments;+----+------+| id | name |+----+------+|  1 | dev  ||  2 | test ||  3 | ops  |+----+------+3 rows in set (0.00 sec)

   * 插入正常資料 （部門編號存在於部門表中）

mysql> INSERT INTO empoyees SELECT NULL, ‘M‘, 22, 2;Query OK, 1 row affected (0.01 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> SELECT * FROM empyees;ERROR 1146 (42S02): Table ‘mytest.empyees‘ doesn‘t existmysql> SELECT * FROM empoyees;+----+-----+-----+------------+| id | sex | age | department |+----+-----+-----+------------+|  1 | M   |  22 |          2 |+----+-----+-----+------------+1 row in set (0.01 sec)

   * 插入非正常資料 (部門編號不存在於部門表中)　[ 部門表中不存在id為4的列 ]

mysql> INSERT INTO empoyees SELECT NULL, ‘M‘, 22, 4;ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mytest`.`empoyees`, CONSTRAINT `empoyees_ibfk_1` FOREIGN KEY (`department`) REFERENCES `departments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)

   * 修改部門表資料 [ 員工表中已有資料關聯部門表中id為2 ]

mysql> SELECT * FROM empoyees;+----+-----+-----+------------+| id | sex | age | department |+----+-----+-----+------------+|  1 | M   |  22 |          2 |+----+-----+-----+------------+1 row in set (0.01 sec)mysql> UPDATE departments SET id = 4 WHERE id=2;Query OK, 1 row affected (0.04 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM empoyees;+----+-----+-----+------------+| id | sex | age | department |+----+-----+-----+------------+|  1 | M   |  22 |          4 |+----+-----+-----+------------+1 row in set (0.01 sec)

   * 刪除部門表資料 [ 員工表中已有資料關聯部門表中id為4 ]

mysql> SELECT * FROM empoyees;+----+-----+-----+------------+| id | sex | age | department |+----+-----+-----+------------+|  1 | M   |  22 |          2 |+----+-----+-----+------------+1 row in set (0.01 sec)mysql> DELETE FROM departments WHERE id = 4;Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM empoyees;+----+-----+-----+------------+| id | sex | age | department |+----+-----+-----+------------+|  1 | M   |  22 |       NULL |+----+-----+-----+------------+1 row in set (0.00 sec)

4. 總結

以需求驅動技術，技術本身沒有優略之分，只有業務之分。

本文出自 “sea” 部落格，請務必保留此出處http://lisea.blog.51cto.com/5491873/1943689

MySQL DDL操作--------外鍵最佳實戰