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

Python全棧之路系列之MySQL外鍵

先來個例子來說明什么是外鍵以及外鍵的作用，so，XO公司現(xiàn)正處于一種迅速發(fā)展的狀態(tài)，從最初的12人的團(tuán)隊(duì)發(fā)展到現(xiàn)在的300人，那么問題就來了，發(fā)展的越快，人員與部門就越來越多，這是老大要求我們做一個人員管理系統(tǒng)，用于查詢?nèi)肼毴藛T的信息等。

起初我們想用一個表來實(shí)現(xiàn)所有的人員統(tǒng)計(jì)，創(chuàng)建Personnel庫,用于存放公司員工的信息，指令如下：

CREATE DATABASE personnel DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

然后在創(chuàng)建一個人員信息表，在這個person_info表中，id和name列是聯(lián)合主鍵，引擎是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,
  -- 手機(jī)號碼
  `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;

插入成員數(shù)據(jù)

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

查看插入的數(shù)據(jù)

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

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

所以，外鍵的作用就出來了，那么現(xiàn)在的表設(shè)計(jì)就改變了，變成什么樣的呢？

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

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

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

先刪除之前創(chuàng)建的person_info表

DROP TABLE person_info;

創(chuàng)建part職務(wù)表

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

插入部門數(shù)據(jù)

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

查看插入的部門信息

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

創(chuàng)建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;

設(shè)置外鍵

把person_info表中的part_nid列和part表中的nid列做一個外鍵關(guān)聯(lián)，外鍵的名稱為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條數(shù)據(jù)

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"
    )

上面插入的數(shù)據(jù)都是合法的，那么如果我們插入不合法的數(shù)據(jù)會怎樣呢？比如說插入一條在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，是不是就報錯了呢？沒做，這就是外鍵的約束，如果說你插入的數(shù)據(jù)中，part_nid列中的數(shù)據(jù)在part表的nid列沒有的話，那么是萬萬不可以的,

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

#Python全棧之路