4Python全棧之路系列之MYSQL外鍵

Python全棧之路系列之MySQL外鍵

先來個例子來說明什么是外鍵以及外鍵的作用，so，XO公司現正處于一種迅速發展的狀態，從最初的12人的團隊發展到現在的300人，那么問題就來了，發展的越快，人員與部門就越來越多，這是老大要求我們做一個人員管理系統，用于查詢入職人員的信息等。

起初我們想用一個表來實現所有的人員統計，創建Personnel庫,用于存放公司員工的信息，指令如下：

CREATE DATABASE personnel DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

然后在創建一個人員信息表，在這個person_info表中，id和name列是聯合主鍵，引擎是InnoDB，字符集是utf8格式的

CREATE TABLE `person_info` (
  -- 人員ID
  `id` int(11) NOT NULL AUTO_INCREMENT,
  -- 人員姓名
  `name` varchar(10) NOT NULL,
  -- email
  `email` varchar(15) DEFAULT NULL,
  -- 手機號碼
  `phone` char(11) NOT NULL,
  -- 部門
  `department` varchar(32) DEFAULT NULL,
  -- 職位
  `position` varchar(30) DEFAULT NULL,
  -- 說明描述
  `caption` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

插入成員數據

INSERT INTO person_info(name,email,phone,department,position,caption) VALUES ('a', 'a@anshengme.com', '13800138000', 'XO股份有限公司公司-技術部-Python開發', 'Python API開發', '我是一名Python開發工程師，專注于PYthon API開發');
INSERT INTO person_info(name,email,phone,department,position) VALUES ('b', 'b@anshengme.com', '13800138000', 'XO股份有限公司公司-技術部-JAVA開發', 'JAVA APP開發');
INSERT INTO person_info(name,email,phone,department,position) VALUES ('c', 'c@anshengme.com', '13800138000', 'XO股份有限公司公司-技術部-前端', 'JavaScript');
INSERT INTO person_info(name,email,phone,department,position) VALUES ('d', 'd@anshengme.com', '13800138000', 'XO股份有限公司公司-技術部-DBA', 'MySQL DBA');
INSERT INTO person_info(name,email,phone,department,position) VALUES ('e', 'e@anshengme.com', '13800138000', 'XO股份有限公司公司-技術部-服務器組', 'Linux');

查看插入的數據

mysql> select * from person_info;
+----+------+-----------------+-------------+---------------------------------------------------+------------------+---------------------------------------------------------------+
| id | name | email           | phone       | department                                        | position         | caption                                                       |
+----+------+-----------------+-------------+---------------------------------------------------+------------------+---------------------------------------------------------------+
|  1 | a    | a@anshengme.com | 13800138000 | XO股份有限公司公司-技術部-Python開發              | Python API開發   | 我 是一名Python開發工程師，專注于PYthon API開發                |
|  2 | b    | b@anshengme.com | 13800138000 | XO股份有限公司公司-技術部-JAVA開發                | JAVA APP開發     | NULL                                                          |
|  3 | c    | c@anshengme.com | 13800138000 | XO股份有限公司公司-技術部-前端                    | JavaScript       | NULL                                                          |
|  4 | d    | d@anshengme.com | 13800138000 | XO股份有限公司公司-技術部-DBA                     | MySQL DBA        | NULL                                                          |
|  5 | e    | e@anshengme.com | 13800138000 | XO股份有限公司公司-技術部-服務器組                | Linux            | NULL                                                          |
+----+------+-----------------+-------------+---------------------------------------------------+------------------+---------------------------------------------------------------+
5 rows in set (0.00 sec)

so，我們只是插入了上面5條數據而已，那么如果有成千上萬條的數據，department是不是都是重復的呢？因為么個人肯定都是屬于一個部門的，還可能屬于這個部門下面某個組的，而且上面的部門字段所占用的字符只有這個么幾個，如果有個變態的老大，必須把部門名車個搞到很長很長，那么這樣做是不是很占用空間呢？而且查詢的時候也沒有那么快。

所以，外鍵的作用就出來了，那么現在的表設計就改變了，變成什么樣的呢？

person_info表被拆封成兩個表，分為part部門表和person_info人員信息表，

part表有nid和caption列，nid為主鍵并自增，caption存放公司所有的部門等。

person_info依舊是人員信息表，擁有的字段和上面表中的設置是一樣的，id和name依舊是主鍵，id自增，唯一改變的就是part_nid列關聯了part表中的nid列，表設計如下圖：

先刪除之前創建的person_info表

DROP TABLE person_info;

創建part職務表

CREATE TABLE part (
  nid int(11) NOT NULL AUTO_INCREMENT,
  caption varchar(32) NOT NULL,
  PRIMARY KEY (nid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入部門數據

INSERT INTO part(caption) VALUES("XO股份有限公司公司-技術部-JAVA開發"),("XO股份有限公司公司-技術部-前端"),("XO股份有限公司公司-技術部-DBA"),("XO股份有限公司公司-技術部-服務器組"),("XO股份有限公司公司-技術部-Python開發");

查看插入的部門信息

mysql> select * from personnel.part;
+-----+---------------------------------------------------+
| nid | caption                                           |
+-----+---------------------------------------------------+
|   1 | XO股份有限公司公司-技術部-JAVA開發                |
|   2 | XO股份有限公司公司-技術部-前端                    |
|   3 | XO股份有限公司公司-技術部-DBA                     |
|   4 | XO股份有限公司公司-技術部-服務器組                |
|   5 | XO股份有限公司公司-技術部-Python開發              |
+-----+---------------------------------------------------+
5 rows in set (0.00 sec)

創建person_info人員信息表

CREATE TABLE `person_info` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `email` varchar(20) NOT NULL,
  `phone` char(11) NOT NULL,
  `part_nid` int(11) NOT NULL,
  `position` char(20) NOT NULL,
  `caption` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`nid`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

設置外鍵

把person_info表中的part_nid列和part表中的nid列做一個外鍵關聯，外鍵的名稱為person_ibfk_1

alter table person_info add constraint person_ibfk_1 foreign key person_info(`part_nid`) REFERENCES part(`nid`);

刪除外鍵

刪除person_info表中的person_ibfk_1外鍵

alter table person_info drop foreign key person_ibfk_1;

往成員表中插入6條數據

INSERT INTO person_info (
    NAME,
    email,
    phone,
    part_nid,
    position
)
VALUES
    (
        "as",
        "as@anshengme.com",
        13800138000,
        5,
        "Python"
    ),
    (
        "ansheng",
        "as@anshengme.com",
        13800138000,
        5,
        "Python"
    ),
    (
        "a",
        "as@anshengme.com",
        13800138000,
        5,
        "Python"
    ),
    (
        "v",
        "as@anshengme.com",
        13800138000,
        5,
        "Python"
    ),
    (
        "b",
        "as@anshengme.com",
        13800138000,
        5,
        "Python"
    ),
    (
        "w",
        "as@anshengme.com",
        13800138000,
        5,
        "Python"
    )

上面插入的數據都是合法的，那么如果我們插入不合法的數據會怎樣呢？比如說插入一條在part表中不存在的部門會怎樣？

mysql> use personnel;
Database changed
mysql> INSERT INTO person_info(name,email,phone,part_nid,position) VALUES("pwd","pwd@anshengme.com",13800138000,10,"Python");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`personnel`.`person_info`, CONSTRAINT `person_ibfk_1` FOREIGN KEY (`part_nid`) REFERENCES `part` (`nid`))

so，是不是就報錯了呢？沒做，這就是外鍵的約束，如果說你插入的數據中，part_nid列中的數據在part表的nid列沒有的話，那么是萬萬不可以的,

上述是一對多的關系，多對多和連表查詢會在下一篇文章中介紹，其實很簡單

#Python全棧之路