柚子快報激活碼778899分享：數(shù)據(jù)庫 MySQL中的外鍵約束

http://yzkb.51969.com/

外鍵約束

在實(shí)際開發(fā)的項(xiàng)目中，一個健壯數(shù)據(jù)庫中的數(shù)據(jù)一定有很好的參照完整性。例如，員工管理系統(tǒng)中有員工表和部門表，如果員工表的部門編號字段使用了部門編號20，而部門表中的編號20卻被刪除了，那么就會產(chǎn)生垃圾數(shù)據(jù)或錯誤數(shù)據(jù)。為保證數(shù)據(jù)的完整性，可以在員工表中添加外鍵約束。

添加外鍵約束

外鍵是數(shù)據(jù)表中的一個特殊字段，它引用另一張數(shù)據(jù)表中的一列或多列，被引用的列應(yīng)該具有主鍵約束或唯一約束。例如在員工表emp的deptno字段上添加外鍵約束，引用部門表dept的主鍵字段deptno，如此就通過外鍵加強(qiáng)了員工表和部門表數(shù)據(jù)之間的關(guān)聯(lián)。

對于兩個具有關(guān)聯(lián)關(guān)系的數(shù)據(jù)表來說，相關(guān)聯(lián)字段中主鍵所在的數(shù)據(jù)表就是主表，外鍵所在的數(shù)據(jù)表就是從表。

在MySQL中為從表添加外鍵約束的語法格式如下。

ALTER TABLE 從表名 ADD CONSTRAINT [外鍵名稱] FOREIGN KEY(外鍵字段名) REFERENCES 主表名(主鍵字段名)；

在上述語法格式中，ADD CONSTRAINT表示添加約束；外鍵名稱是可選參數(shù)，用來指定添加的外鍵約束的名稱；FOREIGN KEY表示外鍵約束；使用REFERENCES指定創(chuàng)建的外鍵引用哪個表的主鍵。

接下來，根據(jù)上述語法格式，為員工表emp添加外鍵約束，具體語句及執(zhí)行結(jié)果如下。

mysql> ALTER TABLE emp ADD CONSTRAINT wjname FOREIGN KEY(deptno) REFERENCES dept(deptno);

Query OK, 9 rows affected (0.06 sec)

Records: 9 Duplicates: 0 Warnings: 0

添加外鍵成功后，可以使用SHOW CREATE TABLE語句查看員工表emp的創(chuàng)建語句，查詢語句及結(jié)果如下。

mysql> SHOW CREATE TABLE emp;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table

|

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| emp | CREATE TABLE `emp` (

`empno` int NOT NULL,

`ename` varchar(20) NOT NULL,

`job` varchar(20) NOT NULL,

`mgr` int DEFAULT NULL,

`sal` decimal(7,2) DEFAULT NULL,

`comm` decimal(7,2) DEFAULT NULL,

`deptno` int DEFAULT NULL,

PRIMARY KEY (`empno`),

UNIQUE KEY `ename` (`ename`),

KEY `wjname` (`deptno`),

CONSTRAINT `wjname` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.01 sec)

在上述輸出結(jié)果中，第12行表示創(chuàng)建了一個和外鍵名稱同名的索引，第13行表示deptmo字段上創(chuàng)建了名稱為wjname的外鍵，wjname外鍵引用部門表dept中的deptno字段。

在為表添加外鍵約束時，需要注意以下情況。

建立外鍵的表必須使用InnoDB引擎(默認(rèn)的存儲引擎)，不能是臨時表，因?yàn)樵贛ySQL中只有InnoDB引擎才允許使用外鍵。

定義的外鍵名稱不能加引號，如CONSTRAINT'FK_ID'或CONSTRAINT"FKID”都是錯誤的。

外鍵所在列的數(shù)據(jù)類型必須和主表中主鍵對應(yīng)列的數(shù)據(jù)類型相同。

需要大家注意的是外鍵名稱是自定義的，根據(jù)表的具體結(jié)構(gòu)來定義，這里的外鍵名稱是wjname，這是為了大家更好的理解。

操作關(guān)聯(lián)表

在實(shí)際開發(fā)中，需要根據(jù)實(shí)體的內(nèi)容設(shè)計(jì)數(shù)據(jù)表，實(shí)體間會有各種關(guān)聯(lián)關(guān)系，因此數(shù)據(jù)表之間也存在著各種關(guān)聯(lián)關(guān)系。下面對數(shù)據(jù)表的關(guān)聯(lián)關(guān)系、關(guān)聯(lián)表添加數(shù)據(jù)、關(guān)聯(lián)表刪除數(shù)據(jù)進(jìn)行講解。

1.數(shù)據(jù)表的關(guān)聯(lián)關(guān)系

根據(jù)數(shù)據(jù)關(guān)系，MySQL中數(shù)據(jù)表的關(guān)聯(lián)關(guān)系可以分為一對一、多對一、多對多3種。這3種關(guān)聯(lián)關(guān)系具體介紹如下。

(1)一對一。

一對一在實(shí)際生活中比較常見，例如人與身份證之間就是一對一的關(guān)系，一個人對應(yīng)一張身份證，一張身份證只能匹配一個人。

一對一關(guān)系的兩張數(shù)據(jù)表建立外鍵時，要分清主從關(guān)系。例如，身份證是人的附屬，身份證需要人的存在才有意義。同樣，在數(shù)據(jù)表的主從關(guān)系中，從表需要主表的存在才有意義。假如有身份證和人兩張數(shù)據(jù)表，那么人為主表，身份證為從表，需要在身份證表中建立外鍵。

需要注意的是，一對一關(guān)聯(lián)關(guān)系在數(shù)據(jù)庫中并不常見，因?yàn)橐赃@種方式存儲的信息通常會放在一個表中。在實(shí)際開發(fā)中，一對一關(guān)聯(lián)關(guān)系可以應(yīng)用于如下場景。

分割具有很多列的表。

由于安全原因而隔離表的一部分。

保存臨時數(shù)據(jù)，并且可以毫不費(fèi)力地通過刪除保存臨時數(shù)據(jù)的表而刪除這些數(shù)據(jù)。

(2)多對一。

多對一關(guān)聯(lián)是數(shù)據(jù)表之間最常見的一種關(guān)聯(lián)關(guān)系。例如員工與部門之間的關(guān)系，個部門可以有多個員工，而一個員工不能屬于多個部門；也就是說部門表中的一行記錄在員工表中可以有多行匹配的記錄，但員工表中的一行記錄在部門表中只能有一行匹配的記錄。

通過之前的講解可以知道，表之間的關(guān)系是通過外鍵建立的。在多對一的表關(guān)系中，應(yīng)該將外鍵添加在“多”的一方，否則會造成數(shù)據(jù)的冗余。

3)多對多。

多對多也是數(shù)據(jù)表之間的一種關(guān)聯(lián)關(guān)系。例如學(xué)生與課程之間的關(guān)系，一個學(xué)生可以選擇多門課程，當(dāng)然一門課程也供多個學(xué)生選擇；也就是說學(xué)生表中一行記錄在課程表中可以有多行匹配的記錄，課程表中的一行記錄在學(xué)生表中也可以有多行匹配的記錄。

通常情況下，為實(shí)現(xiàn)多對多關(guān)聯(lián)關(guān)系，需要定義一張中間表(稱為連接表)。中間表會存在兩個外鍵，分別引用課程表和學(xué)生表。

2.關(guān)聯(lián)表添加數(shù)據(jù)

前面已經(jīng)為員工表emp添加外鍵約束。此時員工表emp和部門表dept之間是多對一的關(guān)聯(lián)關(guān)系。下面演示在這兩個關(guān)聯(lián)表中添加數(shù)據(jù)。

(1)往主表dept中插入數(shù)據(jù)。因?yàn)閺谋韊mp的外鍵列只能插入所引用的列(部門表的deptmo字段)中存在的值，所以如果要為兩個數(shù)據(jù)表添加數(shù)據(jù)，就需要先為主表dept添加數(shù)據(jù)，插入數(shù)據(jù)的SQL語句如下。

mysql> INSERT INTO dept VALUES(50,'人力資源部');

Query OK, 1 row affected (0.01 sec)

(2)往從表emp中插入數(shù)據(jù)。在主表中添加的數(shù)據(jù)中，主鍵 deprno的值包含10、20、30、40和50，由于員工表cmp的外鍵引用部門表的主鍵deplto，因此在往員工表emp中添加數(shù)據(jù)時，其deptno字段的值只能是10、20、30、40和50，不能使用其他的值，具體語句如下。

mysql> INSERT INTO emp VALUES

-> (886,'華佗','運(yùn)營專員',9839,3500,200,40),

-> (888,'曹操','人事專員',9966,3500,NULL,50);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

數(shù)據(jù)插入成功后，如果要查詢?nèi)肆Y源部有哪些員工，可以使用鏈接查詢完成，也可以使用子查詢完成。例如使用內(nèi)連接查詢完成查詢需求，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT e.*,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname='人力資源部';

+-------+--------+--------------+------+---------+------+--------+-----------------+

| empno | ename | job | mgr | sal | comm | deptno | dname |

+-------+--------+--------------+------+---------+------+--------+-----------------+

| 888 | 曹操 | 人事專員 | 9966 | 3500.00 | NULL | 50 | 人力資源部 |

+-------+--------+--------------+------+---------+------+--------+-----------------+

1 row in set (0.00 sec)

從上述執(zhí)行結(jié)果可以得出，人力資源部只有1名員工。需要注意的是，外鍵約束是為了保證數(shù)據(jù)的完整性和統(tǒng)一性，主表和從表中進(jìn)行數(shù)據(jù)的新增、編輯、刪除時需要遵循外鍵約束的要求，但是對數(shù)據(jù)的查詢沒有約束性。

3.關(guān)聯(lián)表刪除數(shù)據(jù)

除了為關(guān)聯(lián)表添加數(shù)據(jù)，某些情況下也存在刪除關(guān)聯(lián)表中數(shù)據(jù)的需求。例如，因?yàn)楣窘M織架構(gòu)調(diào)整，需要取消人力資源部，此時就需要在數(shù)據(jù)庫中將人力資源部刪除。下面演示刪除關(guān)聯(lián)表中部門表的數(shù)據(jù)。

由于員工表emp和部門表dept之間使用外鍵進(jìn)行了關(guān)聯(lián)，因此主表dept中已經(jīng)被引用的值不能直接刪除。如果要刪除人力資源部，需要先將人力資源部中的員工刪除，或者轉(zhuǎn)移到其他部門，又或者不分配部門(部門編號設(shè)置為NULL)。在此選擇先刪除人力資源部中的員工，再刪除部門表中的人力資源部。

(1)刪除從表emp中屬于人力資源部的員工信息，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> DELETE FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='人力資源部');

Query OK, 1 row affected (0.01 sec)

從上述語句的執(zhí)行結(jié)果可以得出，刪除語句執(zhí)行成功。為驗(yàn)證刪除的情況，可以在員工表emp中查詢屬于人力資源部的員工信息，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT e.*,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname='人力資源部';

Empty set (0.00 sec)

使用SELECT語句也可以直接查詢：

mysql> select*from emp;

+-------+-----------+--------------+------+---------+---------+--------+

| empno | ename | job | mgr | sal | comm | deptno |

+-------+-----------+--------------+------+---------+---------+--------+

| 886 | 華佗 | 運(yùn)營專員 | 9839 | 3500.00 | 200.00 | 40 |

| 911 | 王五 | 分析員 | 9866 | 4000.00 | NULL | 20 |

| 935 | 陳十一 | 經(jīng)理 | 9839 | 3500.00 | NULL | 10 |

| 936 | 吳九 | 銷售 | 9698 | 2250.00 | 1000.00 | 30 |

| 951 | 鄭十 | 銷售 | 9698 | 2500.00 | 0.00 | 30 |

| 952 | 周八 | 銷售 | 9698 | 2250.00 | 500.00 | 30 |

| 969 | 蕭二 | 保潔 | 9698 | 2000.00 | NULL | 30 |

| 985 | 劉一 | 董事長 | NULL | 6000.00 | NULL | 10 |

| 991 | 趙六 | 分析員 | 9566 | 4000.00 | NULL | 20 |

| 994 | 孫七 | 銷售 | 9698 | 2500.00 | 300.00 | 30 |

+-------+-----------+--------------+------+---------+---------+--------+

10 rows in set (0.00 sec)

從執(zhí)行結(jié)果來看，已經(jīng)沒有了人力資源部員工曹操的信息了，證明我們已經(jīng)成功刪除了。

這個刪除是比較麻煩的，我們也可以直接使用在刪除數(shù)據(jù)表的部分?jǐn)?shù)據(jù)中使用的語句：

mysql> DELETE FROM emp WHERE ename='華佗';

Query OK, 1 row affected (0.00 sec)

這個相對來說簡單多了。簡單粗暴還好用。

(2)刪除主表dept中的數(shù)據(jù)。此時從表emp中已經(jīng)沒有數(shù)據(jù)引用主表dept中主鍵值為人力資源部的記錄，可以刪除主表dept中部門名稱為人力資源部的記錄，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> DELETE FROM dept WHERE dname='人力資源部';

Query OK, 1 row affected (0.00 sec)

從上述語句的執(zhí)行結(jié)果可以得出，刪除語句執(zhí)行成功。為驗(yàn)證刪除的情況，可以對名稱為人力資源部的部門信息進(jìn)行查詢，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> SELECT * FROM dept;

+--------+-----------+

| deptno | dname |

+--------+-----------+

| 20 | 研究院 |

| 10 | 裁決室 |

| 40 | 運(yùn)營部 |

| 30 | 銷售部 |

+--------+-----------+

4 rows in set (0.00 sec)

可以看到人力資源部已經(jīng)刪除了。

運(yùn)營部也沒有人了，試試能不能刪除運(yùn)營部：

mysql> DELETE FROM dept WHERE dname='運(yùn)營部';

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM dept;

+--------+-----------+

| deptno | dname |

+--------+-----------+

| 20 | 研究院 |

| 10 | 裁決室 |

| 30 | 銷售部 |

+--------+-----------+

3 rows in set (0.00 sec)

從執(zhí)行結(jié)果來看，運(yùn)營部也成功刪除了，也是簡單粗暴，既然有簡單的辦法大家就用簡單的方法就行。

刪除外鍵約束

在實(shí)際開發(fā)中，根據(jù)業(yè)務(wù)邏輯需求，如果需要解除兩個表之間的關(guān)聯(lián)關(guān)系，就需要刪除外鍵約束。刪除外鍵約束的語法格式如下;

ALTER TABLE 外鍵所在表的表名（從表表名） DROP FOREIGN KEY 外建名;

接下來，將員工表emp中的外鍵約束刪除，具體SQL語句及執(zhí)行結(jié)果如下。

mysql> ALTER TABLE emp DROP FOREIGN KEY wjname;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

語法執(zhí)行成功后，查看員工表emp的創(chuàng)建信息，查詢語句及執(zhí)行結(jié)果如下。

mysql> SHOW CREATE TABLE emp;

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table

|

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| emp | CREATE TABLE `emp` (

`empno` int NOT NULL,

`ename` varchar(20) NOT NULL,

`job` varchar(20) NOT NULL,

`mgr` int DEFAULT NULL,

`sal` decimal(7,2) DEFAULT NULL,

`comm` decimal(7,2) DEFAULT NULL,

`deptno` int DEFAULT NULL,

PRIMARY KEY (`empno`),

UNIQUE KEY `ename` (`ename`),

KEY `wjname` (`deptno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

從執(zhí)行結(jié)果來看，員工表emp中的外鍵已經(jīng)被成功刪除。

柚子快報激活碼778899分享：數(shù)據(jù)庫 MySQL中的外鍵約束

http://yzkb.51969.com/

精彩文章